VLOOKUP returns #N/A but the value is right there: numbers stored as text and trailing spaces

#googlesheets #productivity #spreadsheets #tutorial

You can see the key in the table, and VLOOKUP(..., FALSE) still says #N/A. That's almost never a missing row. Exact match compares values, not what the cell looks like. The two usual culprits are a number vs text mismatch, and a trailing space.

Recreate both

	A (sku)	B (name)	D (lookup)	E
2	101 (number)	Widget	`'101` (text)	`=VLOOKUP(D2,A2:B2,2,FALSE)` → #N/A
3	`apple` (trailing space)	Fruit	apple	`=VLOOKUP(D3,A3:B3,2,FALSE)` → #N/A

Check the keys instead of guessing

=ISNUMBER(A2)   → TRUE
=ISTEXT(D2)     → TRUE     same digits, different types
=LEN(A3)        → 6
=LEN(D3)        → 5        same word, different lengths

Alignment is a hint (text left, numbers right), but ISNUMBER/ISTEXT and LEN are the proof.

A quick confirmation trick: retype D2 as a plain number (no apostrophe). E2 becomes Widget. Put the apostrophe back and #N/A returns. That toggle tells you it's a type problem, not a missing row.

The trap: TRIM on the wrong side

=VLOOKUP(TRIM(D3),A3:B3,2,FALSE)   → still #N/A

The extra space lives in the table, not in the lookup value. Trimming D3 can't remove it.

Fixes

Number vs text – convert the side that's wrong:

=VLOOKUP(VALUE(D2),A2:B2,2,FALSE)   → Widget

If instead the table holds text and your lookup is a number, convert the lookup with TO_TEXT(D2) (or clean the table column). Don't wrap both sides and hope.

Space in the table – trim the table side:

=VLOOKUP(D3,ARRAYFORMULA({TRIM(A3:A),B3:B}),2,FALSE)

For real data, a cleaned helper column is easier to maintain. (In locales that use ; as the argument separator, the comma between columns inside { } becomes a backslash: {TRIM(A3:A)\B3:B}.)

Still missing after TRIM? Check LEN again: Google's TRIM doesn't remove a non-breaking space, CHAR(160) (common in data pasted from the web). Turn it into a normal space first: TRIM(SUBSTITUTE(A3,CHAR(160)," ")).

Don't reach for IFNA yet

IFNA(VLOOKUP(...),"") is fine for keys that really aren't there. When the row should match, hiding the #N/A just hides the type bug. Fix the keys first, then add IFNA for true misses.

Originally published on SheetError, a free library of Google Sheets error fixes (I run it).

This article was created with the help of AI.

DEV Community