VLOOKUP returns #N/A on values that look identical because they are not identical: a trailing space, a number stored as text, or a curly quote makes Excel see two different values. The fix is almost never the formula; it is the data. Here are the six causes ranked by how often they bite, with the fix for each.
The six causes, ranked
1. Trailing or leading spaces
“ACME Corp ” does not match “ACME Corp”. Wrap the lookup value: =VLOOKUP(TRIM(A2),table,2,FALSE), and TRIM the lookup column itself. This is the single most common cause in exported data.
2. Number stored as text on one side
A customer ID stored as text (“1042”) will not match the number 1042. Check with =ISTEXT() on both sides. Convert text to numbers with =VALUE(), or numbers to text with =TEXT(A2,"0"), so both sides agree. The green triangle in the cell corner is Excel telling you this is coming.
3. You omitted FALSE
=VLOOKUP(A2,table,2) without a fourth argument defaults to approximate match, which requires the table sorted ascending and happily returns the wrong row when it is not. Always write the FALSE: =VLOOKUP(A2,table,2,FALSE). Approximate match has legitimate uses (tax brackets, grading scales); accidental use is just wrong answers.
4. Invisible characters
Web exports carry non-breaking spaces (CHAR(160)) that TRIM does not remove, and word processors convert straight quotes to curly ones. Fix: =TRIM(SUBSTITUTE(A2,CHAR(160)," ")), and SUBSTITUTE for curly quotes. =LEN(A2) against the length you expect reveals the stowaways.
5. The answer is left of the key
VLOOKUP only looks rightward from the lookup column. Restructure, use INDEX/MATCH, or use XLOOKUP (Excel 2021/365), which looks both directions and replaces this whole class of problem: =XLOOKUP(A2,keys,results,"not found").
6. Inconsistent formats in the key column
Phone numbers as 555.123.4567 in one sheet and (555) 123-4567 in the other will never match, even though every character of meaning is identical. Same for dates stored as text in one file and serials in the other. Standardize both sides to one format first; this is exactly the pass our free cleaning tool automates, with a preview of every change.
The diagnostic, in order
=A2=B2on a pair you believe should match. FALSE confirms a value difference.=LEN(A2)vs=LEN(B2). Different lengths mean whitespace or invisible characters.=ISTEXT(A2)vs=ISTEXT(B2). Mismatch means a type problem.- Only after those three pass, suspect the formula itself (range, column index, FALSE).
FAQ
Should I just switch to XLOOKUP?
If your Excel has it (2021 or 365), yes for new work: exact match is the default, it looks left, and it has a built-in not-found argument. It still cannot match “ACME Corp ” to “ACME Corp”, so the data fixes in this guide apply unchanged.
Why does VLOOKUP work for some rows and not others?
Mixed data: part of the column is clean, part has spaces or type mismatches. The diagnostic above applied to one failing row will find the pattern.
Can IFERROR fix #N/A?
IFERROR hides the error, it does not fix the match. =IFERROR(VLOOKUP(...),"not found") is good presentation; using it to suppress mismatches you have not investigated is how wrong totals ship.
Leave a Reply