VLOOKUP Returning #N/A? Common Causes and How to Fix Them

Cause 1: Text vs. Numbers

The most common cause. Looking up “123” (text) won't find 123 (number), and vice versa.

Test: Try =A1=B1 where A1 is your lookup value and B1 is a value in the table. If it returns FALSE but they look identical, you have a type mismatch.

Fix: Convert both to the same type:

• Text to number: =VLOOKUP(VALUE(A1), range, col, FALSE)
• Number to text: =VLOOKUP(TEXT(A1,“0”), range, col, FALSE)

Cause 2: Extra Spaces

“Apple” doesn't match “Apple ” (trailing space) or “ Apple” (leading space).

Test: =LEN(A1) — if it's longer than expected, there are hidden spaces.

Fix: =VLOOKUP(TRIM(A1), range, col, FALSE) and clean the lookup table with TRIM too.

Cause 3: Non-Breaking Spaces

TRIM doesn't remove all space characters. Non-breaking spaces (common in web data) look identical but don't match.

Fix: =VLOOKUP(SUBSTITUTE(TRIM(A1),CHAR(160),“”), range, col, FALSE)

Cause 4: Lookup Value Not in First Column

VLOOKUP only searches the first column of your table_array. If your lookup value is in column 2, it won't be found.

Fix: Adjust your table_array to start with the lookup column. Or switch to INDEX/MATCH or XLOOKUP which can search any column.

Cause 5: Approximate Match Finding Wrong Value

Using TRUE (or omitting the last argument) does approximate matching, which requires sorted data. Unsorted data gives wrong results.

Fix: Almost always use FALSE for exact match: =VLOOKUP(A1, range, col, FALSE)

Cause 6: Value Really Doesn't Exist

Sometimes the #N/A is correct — the value truly isn't in the table. Handle it gracefully:

Fix: =IFERROR(VLOOKUP(A1, range, col, FALSE), “Not Found”)

Or with IFNA (Excel 2013+): =IFNA(VLOOKUP(...), “Not Found”)

Debugging Tip: MATCH First

Use MATCH to isolate whether the lookup value is found:

=MATCH(A1, LookupColumn, 0)

If MATCH returns #N/A, the problem is with finding the value. If MATCH works but VLOOKUP doesn't, the problem is with the column reference.

Official Resources

• VLOOKUP function — Microsoft's guide
• Correct #N/A error — troubleshooting lookups