VLOOKUP Not Working? 8 Causes of #N/A and How to Fix Them
VLOOKUP returning #N/A or the wrong value? The 8 most common causes, from extra spaces and text numbers to unlocked ranges, with a fix for each.
By Ayoub Farid · · 2 min read
You can see the value in the table. VLOOKUP says #N/A. This is one of the most common Excel frustrations, and it almost always comes down to one of these eight causes. Work through them in order.
1. Extra spaces
"P-102 " (with a trailing space) isn't "P-102". Imported data is full of these.
Check: =LEN(A3) shows more characters than you can see. Fix: clean the data with TRIM, or trim inside the lookup: =VLOOKUP(TRIM(F2),$A$2:$C$100,3,FALSE).
2. Numbers stored as text (or text stored as numbers)
The table has 1042 as text, your lookup value is the number 1042. They don't match.
Check: one is left-aligned, the other right-aligned, or =ISTEXT(A2) returns TRUE. Fix: convert the column (Data > Text to Columns > Finish), or match types in the formula: =VLOOKUP(F2&"",...) (number to text) or =VLOOKUP(--F2,...) (text to number).
3. The lookup column isn't first
VLOOKUP only searches the first column of table_array. If your codes are in column B and the range starts at A, it's looking in the wrong place.
Fix: start the range at the lookup column, or use XLOOKUP / INDEX MATCH.
4. The range moved when you filled down
=VLOOKUP(F2,A2:C10,3,FALSE) filled down becomes A3:C11, A4:C12… and top rows fall out of the table.
Fix: lock it: $A$2:$C$10. See absolute references.
5. Missing FALSE (approximate match by accident)
Without the fourth argument, VLOOKUP uses approximate match. On unsorted data, it returns #N/A or, worse, the wrong row with no error.
Fix: always end with FALSE for exact matches.
6. Column number out of range
col_index_num larger than the number of columns in the range gives #REF!. A number less than 1 gives #VALUE!.
Fix: count columns from the first column of your range, not from column A.
7. The value really isn't there
Typos, different spellings ("Coca Cola" vs "Coca-Cola"), or the item is genuinely missing.
Fix: handle it gracefully: =IFERROR(VLOOKUP(...),"Not found"). See IFERROR.
8. Someone inserted a column
A hard-coded column number (3) now points at the wrong column after a column was inserted. No error, just wrong data.
Fix: use XLOOKUP, which references the return column directly, or MATCH to find the column number.
A quick diagnostic routine
- Copy the lookup value and the table value side by side.
=F2=A3→ FALSE means they differ.=LEN(F2)vs=LEN(A3)→ spaces.=ISNUMBER(F2)vs=ISNUMBER(A3)→ type mismatch.
Frequently asked questions
Why does VLOOKUP return #N/A when the value exists?
Usually hidden spaces or a number-vs-text mismatch between the lookup value and the table.
Why does VLOOKUP return the wrong value?
Approximate match is on (missing FALSE), or the column number is wrong.
How do I make VLOOKUP ignore errors?
Wrap it in IFERROR: =IFERROR(VLOOKUP(...),"").
Does XLOOKUP fix these problems?
It avoids the column-number, left-lookup and approximate-by-default issues, but spaces and text-numbers still need cleaning.