How to Fix #N/A Errors in Excel
#N/A stands for "Not Available" — Excel is telling you it could not find the value you asked it to look up. This is the most common error in VLOOKUP, MATCH, HLOOKUP, and XLOOKUP formulas. Here is exactly why it happens and how to fix it.
Find all #N/A errors automatically — upload your spreadsheet and ExcelErrorFinder flags every broken lookup with its cell address and formula.
Free Audit →What Does #N/A Mean?
#N/A (Not Available) is returned by lookup functions when the value they are searching for cannot be found in the lookup range. It is not a broken formula — it is the formula working correctly and reporting that the match does not exist.
The critical question is: should the value exist? If yes, the cause is usually one of the issues below. If genuinely the value does not exist in the lookup range, you can choose to suppress the error or handle it gracefully.
Cause 1: Data Type Mismatch (Most Common)
The most frequent cause. Your lookup value is a number, but the lookup column contains numbers stored as text — or vice versa. They look identical on screen but Excel treats them as different types.
How to spot it: Numbers stored as text are left-aligned; real numbers are right-aligned. Or use =ISNUMBER(A1) — if it returns FALSE for a cell that looks like a number, it is text.
Fix for text-in-lookup-column: Select the lookup column → click the green triangle warning → Convert to Number. Or: =VLOOKUP(VALUE(A2), B:C, 2, 0) — VALUE() converts the lookup value to a number to match the column.
Fix for number-as-lookup-value against text-column: =VLOOKUP(TEXT(A2,"0"), B:C, 2, 0) — TEXT() converts the number to text to match the column.
Cause 2: Extra Spaces
Invisible leading or trailing spaces in either the lookup value or the lookup column prevent matching. " London" (with a leading space) does not equal "London".
Fix: Wrap the lookup value in TRIM: =VLOOKUP(TRIM(A2), B:C, 2, 0). To clean the entire lookup column, use a helper column with =TRIM(B1) copied down, paste as values, and use that column for lookups.
Cause 3: Approximate Match Instead of Exact Match
VLOOKUP's fourth argument controls match type. When omitted or set to TRUE/1, VLOOKUP uses approximate match — which requires the lookup column to be sorted in ascending order. If it is not sorted, VLOOKUP returns incorrect results or #N/A even when the value exists.
Fix: Always use 0 (FALSE) for exact match unless you specifically need approximate: =VLOOKUP(A2, B:C, 2, 0).
Cause 4: The Value Genuinely Does Not Exist
If you are looking up "Product 999" and it simply is not in the lookup table, #N/A is the correct result. This is the formula doing its job.
Fix: Decide how to handle missing values. Use IFERROR to show a custom message instead of the error:
=IFERROR(VLOOKUP(A2, B:C, 2, 0), "Not found")Or use IFNA (which only catches #N/A, not other errors):
=IFNA(VLOOKUP(A2, B:C, 2, 0), "Not found")Cause 5: Lookup Column Is Not the First Column
VLOOKUP always searches in the first (leftmost) column of the table_array. If your lookup column is not first, VLOOKUP searches the wrong column and returns #N/A.
Fix: Rearrange your data so the lookup column is first, OR switch to INDEX/MATCH which can look up any column in any position:
=INDEX(C:C, MATCH(A2, B:B, 0))Cause 6: Wildcard Characters Not Used Correctly
VLOOKUP supports wildcards (* for any characters, ? for one character) only in text lookups with exact match (0). If you are trying to find "Product*" but the column contains "Product A", the wildcard should work — but it will fail if the column is number-formatted.
Fix: Ensure both the lookup value pattern and the lookup column are text type when using wildcards.
How to Suppress #N/A Without Fixing It
If some #N/A errors are expected (not all lookup values will always exist), use IFERROR or IFNA to return a blank or custom text instead of the error cell:
=IFERROR(VLOOKUP(A2, B:C, 2, 0), "") ← returns blank =IFNA(VLOOKUP(A2, B:C, 2, 0), 0) ← returns 0Use IFNA over IFERROR where possible — IFERROR catches all errors (including genuine formula mistakes), while IFNA only catches "not found" results.
Upgrading from VLOOKUP to XLOOKUP
If you are on Microsoft 365, XLOOKUP handles many of these causes more gracefully:
=XLOOKUP(A2, B:B, C:C, "Not found")The fourth argument sets a default value when no match is found — no IFERROR wrapper needed. XLOOKUP also matches in any direction, handles data type mismatches better, and does not require the lookup column to be first.
Frequently Asked Questions
Is #N/A always an error I need to fix?
No. If a lookup value legitimately does not exist in your table, #N/A is the correct result. The decision is whether to display it as #N/A, replace it with a custom message, or use it to flag missing data. Use IFNA to handle expected "not found" cases gracefully.
Why does MATCH return #N/A when the value is clearly in the list?
Usually a data type mismatch or invisible spaces. Test with =EXACT(A2, B2) — if it returns FALSE for two cells that look identical, the values differ (different type, case, or hidden characters). Use VALUE(), TEXT(), or TRIM() to normalise before matching.
Can #N/A in one cell cause errors in other cells?
Yes. Any formula that references a cell containing #N/A will also return #N/A (or another error). Fix the root lookup first — dependent cells will update automatically.