#N/A
#N/A Error in Excel — Causes & Fixes
Excel returns #N/A when a formula cannot find the value it is looking for — most often in VLOOKUP, XLOOKUP, MATCH, or HLOOKUP.
Why this happens
Common causes of #N/A
Lookup value not found
The value in your search column does not exist in the lookup range. Check for typos, trailing spaces, or case mismatches.
Lookup value is a different data type
A number stored as text will not match a true number. Use VALUE() to convert, or make sure both columns are formatted the same way.
Lookup range does not include the match column
In VLOOKUP, the lookup column must be the leftmost column of the table_array. If it is not, use INDEX/MATCH or XLOOKUP instead.
Approximate match on unsorted data
VLOOKUP and MATCH with match_type 1 (approximate) require the lookup column to be sorted ascending. An unsorted range will return wrong results or #N/A.
How to fix it
Step-by-step fixes for #N/A
Wrap with IFERROR to return a fallback
Use =IFERROR(VLOOKUP(A2,Table,2,FALSE),"Not found") to replace #N/A with a readable message or a blank cell.
Use IFNA to catch only #N/A
IFNA is more precise than IFERROR — it only catches #N/A errors and lets other error types pass through so you can see them.
Switch to XLOOKUP with a default value
XLOOKUP has a built-in third argument for the not-found case: =XLOOKUP(A2,B:B,C:C,"Not found"). No wrapper function needed.
Use TRIM and CLEAN to remove hidden characters
If the data came from an import, run =TRIM(CLEAN(A2)) on both the lookup column and the search value to remove invisible spaces and non-printable characters.
Frequently asked questions
How do I hide #N/A without removing the formula?
Wrap the formula in IFERROR: =IFERROR(your_formula,""). The empty string shows a blank cell instead of the error.
What is the difference between IFERROR and IFNA?
IFERROR catches all Excel errors (#N/A, #VALUE!, #REF!, etc.). IFNA only catches #N/A. Use IFNA when you want to handle lookup misses specifically but still see other error types.
Why does VLOOKUP return #N/A when the value exists?
The most common cause is a data-type mismatch — a number stored as text will not match a real number, even if they look identical. Select the lookup value cell and check the format in the toolbar.
Related function references
These functions appear most often in formulas that produce #N/A.
Other Excel errors