alwn_11 AL

VLOOKUP returning #N/A even though the value is clearly there

I have a product list on one sheet and prices on another. VLOOKUP gives #N/A for about half the rows, but I can see the matching codes with my own eyes. Could it be spaces or number formatting?
vlookup excel error
1

Leave A Reply

Log in to post an answer.

1 Answer

A
abhishek87 Nov 04, 2026
alwn_11
almost always spaces or text vs number. quick test: in a spare cell put =LEN(A2) next to a failing code and compare with what you expect, a 6 character code showing 7 has a trailing space. wrap the lookup in TRIM, or run TRIM on both columns once. if lengths match, one side is stored as text and the other as a number, select the column and use Data > Text to Columns > Finish, that converts in one go. and check the 4th argument is FALSE. one more: pasted from a website, you can get CHAR(160) non breaking spaces that TRIM ignores, SUBSTITUTE(A2,CHAR(160),"") deals with those.
Reply