VLOOKUP not working or returning #N/A: 6 common causes
The value is clearly there, yet VLOOKUP says #N/A. These are the usual reasons.
The message you see
VLOOKUP returns #N/AFastest fix
Upload the file and see exactly which rows cause the problem. Free, and the file stays in your browser.
Why this happens
- Extra spaces before or after the lookup value.
- One side is a number, the other is text (123 vs "123").
- The lookup value isn't in the first column of the range.
- Range not locked with $, so it shifts when copied down.
- Approximate match (TRUE/omitted) on unsorted data.
How to fix it
- 1Always use exact match: =VLOOKUP(A2,$D$2:$F$500,2,FALSE).
- 2Trim spaces and make both columns the same type.
- 3Consider XLOOKUP or INDEX/MATCH when the key isn't the first column.
- 4Wrap in IFNA() only after the real mismatch is fixed.
Related guides
Excel
“Excel cannot open the file because the file format or file extension is not valid”
ExcelExcel error “SYLK: File format is not valid” when opening a CSV
Excel“We found a problem with some content” — Excel workbook won't open properly
Still stuck? Ask SheetFix Support