You can see the lookup value with your eyes and VLOOKUP still returns #N/A. That is usually not a missing row. It is a key that does not actually match.

Recreate it on a blank sheet

  1. Open a blank spreadsheet. In A1 type sku, B1 name, D1 lookup, E1 result.
  2. In A2 type 101 as a number. B2 Widget. In D2 type an apostrophe then 101 so Sheets stores it as text. In E2 enter =VLOOKUP(D2,A2:B2,2,FALSE). The cell shows #N/A.

E2 selected. Formula bar shows =VLOOKUP(D2,A2:B2,2,FALSE). E2 is #N/A because A2 is numeric 101 and D2 is text 101.

  1. In A3 type apple then a space. B3 Fruit. D3 apple with no space. In E3 enter the same VLOOKUP against A3:B3. That cell is also #N/A.

E3 selected. Formula bar shows =VLOOKUP(D3,A3:B3,2,FALSE). E3 is #N/A because A3 has a trailing space.

What is actually wrong

  • Numbers and text are not the same key. 101 the number is not '101 the text. VLOOKUP with FALSE does not coerce them.
  • A trailing space in the table is a different string. apple is not apple .
  • TRIM on the lookup value only does not fix a space that lives in the table column.

Check: =LEN(A3) is 6. =LEN(D3) is 5.

Fix

  • Number column: =VLOOKUP(VALUE(D2),A2:B2,2,FALSE) returns Widget. If the table were text and the lookup a number, wrap the table key instead.

F2 selected. Formula bar shows =VLOOKUP(VALUE(D2),A2:B2,2,FALSE) and F2 is Widget. Column E still shows #N/A. F3 is Fruit.

  • Space in the table: trim the table side, for example =VLOOKUP(D3,{TRIM(A3),B3},2,FALSE) returns Fruit. For real data, TRIM the sku column, not only the lookup cell.

If it looks equal and VLOOKUP disagrees, check type and LEN before you rewrite the formula.