Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
Fix the #N/A error
Excel & Google SheetsWhat #N/A means
VLOOKUP returns #N/A when it cannot find the lookup value in the first column of your table range. The error means 'not available' — the match genuinely does not exist, or something is preventing the match from being recognised (a formatting difference, a hidden space, a number stored as text). No data is returned because no row was selected.
Common causes
- The lookup value truly does not exist in the first column of the table range — a typo in the value or in the source data.
- Extra leading or trailing spaces in one side of the comparison (e.g. 'Apple ' vs 'Apple') that are invisible but break exact matching.
- A data-type mismatch: the lookup column contains numbers stored as text while the lookup value is a real number, or vice versa.
- The table range is not anchored with absolute references ($A$2:$C$100) so it shifts when the formula is copied down, cutting off rows.
- The search column is not the leftmost column in the specified range — VLOOKUP always searches column 1 of table_array.
Example fix
Broken
=VLOOKUP(A2,D2:F100,2,0)Fixed
=VLOOKUP(TRIM(A2),$D$2:$F$100,2,0)TRIM removes hidden spaces from the lookup value, and the absolute range ($D$2:$F$100) prevents the table from shifting when the formula is copied.
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
How to fix it
- 1Verify the value exists: filter or Ctrl+F the lookup column for the exact lookup value. Check for typos.
- 2Strip hidden spaces: wrap both sides with TRIM. Change =VLOOKUP(A2,D:F,2,0) to =VLOOKUP(TRIM(A2),D:F,2,0) and apply TRIM to the source column if needed.
- 3Fix data-type mismatches: if the lookup column holds text-numbers, coerce your value with TEXT(A2,"0") or convert the column with Value Paste Special. If the column holds real numbers, wrap the lookup value with VALUE(A2).
- 4Lock the table range: replace =VLOOKUP(A2,D2:F100,2,0) with =VLOOKUP(A2,$D$2:$F$100,2,0) so copying the formula does not shrink the range.
- 5Make sure the search column is the first column in the range. If it is not, either reorder your data or switch to INDEX/MATCH which allows searching any column.
- 6Only use IFERROR to suppress the error after you have confirmed the #N/A is expected (e.g. truly absent items) — never use it to hide a bug.
Stop hunting errors by hand.
Upload your spreadsheet and the Auditor flags every #N/A and broken formula at once — or paste this one formula and get the fix explained.
Frequently asked
Should I just wrap the whole formula in IFERROR to get rid of the red cell?
Only after you have diagnosed why the mismatch happens. IFERROR hides #N/A but does not fix the underlying problem. If a row genuinely has no match and a blank or zero is the right result, then IFERROR or IFNA is appropriate.
Why does VLOOKUP find the value when I type it manually but not from the cell reference?
The cell likely contains invisible characters — a leading space, a non-breaking space, or a line break. Use TRIM(A2) in your formula and LEN(A2) to check the character count versus what you see.
My lookup column has numbers but the table stores them as text — how do I fix it quickly?
In Excel, select the text-number column, click the warning triangle, and choose 'Convert to Number'. In Google Sheets, multiply the column by 1 with a helper formula =B2*1 or use VALUE(B2) to convert.
More on VLOOKUP
See all →Reference
VLOOKUP
Look up a value in the first column of a range and return a value from another column in the same row.
How-toHow to VLOOKUP from another sheet
Pull a value from a different tab with VLOOKUP by prefixing the range with the sheet name. Works in Excel and Google Sheets.
Error fixFix Wrong value
VLOOKUP returns an incorrect value in Excel and Google Sheets — fix wrong column index, approximate match mode, unsorted data, and duplicate lookup keys.
How-toHow to VLOOKUP with multiple criteria
Look up a value that matches two or more conditions using INDEX/MATCH with joined keys. Works in Excel 365 and Google Sheets.
Error fixFix VLOOKUP
VLOOKUP not working in Excel and Google Sheets — diagnose formula errors, wrong syntax, range issues, and when to switch to a better alternative.
How-toHow to fix VLOOKUP returning #N/A
VLOOKUP shows #N/A when it can’t find a match. Here are the real causes — exact-match, spaces, text-vs-number — and how to fix each.