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
#N/A means "not available" — a lookup function searched for a value and found nothing that matched. It is the single most common error with VLOOKUP, XLOOKUP, and MATCH.
Common causes
- The lookup value genuinely is not in the lookup column.
- Hidden spaces or different text case make values look equal but not match.
- Numbers stored as text do not match real numbers (or vice versa).
- VLOOKUP is set to approximate match (TRUE) on unsorted data.
- The lookup value and the lookup column are in different formats (e.g. 00123 vs 123).
Example fix
Broken
=VLOOKUP(D2, A:B, 2, TRUE)Fixed
=VLOOKUP(D2, A:B, 2, FALSE)TRUE does an approximate match and misfires on unsorted data. FALSE forces an exact match.
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
- 1Confirm the value you are searching for actually exists in the first column of the lookup range.
- 2Force an exact match: the last argument of VLOOKUP should be FALSE (or 0).
- 3Clean stray spaces with TRIM, e.g. wrap the lookup value in TRIM() on both sides.
- 4Make the data types match — convert text-numbers with VALUE() or multiply by 1, or convert numbers to text with TEXT().
- 5To show a friendly message instead of #N/A, use the built-in fallback: =XLOOKUP(D2, A:A, B:B, "Not found").
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
Why does my VLOOKUP return #N/A when I can see the value?
Usually a formatting mismatch (text vs number) or trailing spaces. The values look identical but are not equal. Use TRIM and make the data types match.
How do I replace #N/A with text?
XLOOKUP takes a fourth argument for the not-found case. For VLOOKUP, wrap it in IFERROR or IFNA, e.g. =IFNA(VLOOKUP(...), "Not found").