Someone asked a human this. So we asked the AI.
How to Fix the #N/A Error in Excel (Causes and Fixes)
#N/A is Excel politely telling you it looked everywhere and your value just isn't there.
The answer
The #N/A error means "value not available": a lookup function (VLOOKUP, XLOOKUP, HLOOKUP, MATCH, INDEX/MATCH) could not find what you asked it to look for. Fix the cause first; only hide the error if a missing value is genuinely expected.
Find the cause
- Check the lookup value really exists in the first column of the lookup range (for VLOOKUP) or in the lookup array. Look for typos and different spellings.
- Remove stray spaces. A trailing space makes "Smith" and "Smith " different. Wrap the lookup value in
TRIM:=VLOOKUP(TRIM(A2),$E$2:$F$100,2,FALSE). If the spaces are in the table, clean that column too. - Match data types. The number
1001and the text"1001"do not match. Signs: left-aligned numbers, a green corner triangle, orISNUMBERreturning FALSE. Convert text to numbers withVALUE(A2), or multiply by 1 (A2*1), or convert the other side to text withA2&"". - Use exact match. In VLOOKUP the 4th argument must be
FALSE(or0). Leaving it out or usingTRUEon unsorted data causes wrong results or #N/A. - Make sure the lookup column is the first column of the range. VLOOKUP searches only the leftmost column of
table_array. If your key is not on the left, useXLOOKUPorINDEX/MATCH. - Lock the range with `$` when copying the formula down.
E2:F100becomesE3:F101, then the value slides out of the range. Use$E$2:$F$100. - Check the range is big enough to include the row you need.
Handle expected missing values
If some lookups legitimately have no match, show something else instead of #N/A:
=IFNA(VLOOKUP(A2,$E$2:$F$100,2,FALSE),"Not found")- In XLOOKUP use the built-in fourth argument:
=XLOOKUP(A2,$E$2:$E$100,$F$2:$F$100,"Not found")
Prefer IFNA over IFERROR, because IFERROR also hides real mistakes such as #REF! or #NAME?.
In Google Sheets the causes and the IFNA fix are the same.
Example
A2 contains Smith (with a trailing space). The table is in E2:F100, with names in column E and salaries in column F, and Smith is listed as Smith.
=VLOOKUP(A2,$E$2:$F$100,2,FALSE) returns #N/A.
Fix: =VLOOKUP(TRIM(A2),$E$2:$F$100,2,FALSE) returns the salary, for example 52000.
If some names are truly not in the table: =IFNA(VLOOKUP(TRIM(A2),$E$2:$F$100,2,FALSE),"Not found") shows Not found for those rows and the salary for the rest.
Where people go wrong
Wrapping everything in IFERROR straight away. It hides the problem and also masks other errors; find the cause, then use IFNA only for expected misses.
Not noticing numbers stored as text, for example IDs imported from a CSV. Test with
=ISNUMBER(A2)on both the lookup value and the table column, and convert so both sides match.Copying the formula down without
$anchors, so the lookup range shifts and the last rows return #N/A. Use$E$2:$F$100.Using an index number in VLOOKUP larger than the number of columns in the range. That gives #REF!, not #N/A, so check which error you actually have.
The prompt you could have used
My Excel VLOOKUP returns #N/A: =VLOOKUP(A2,$E$2:$F$100,2,FALSE). The value looks identical in the table. List the possible causes (spaces, text vs number, range) and how to check each, and give a fixed formula.
Updated 2 November 2026. Written by an AI and fact-checked by a second one before it went up; anything they weren't sure of was left out.
Someone asked you something like this? Send them a link →
More questions nobody needed to ask
- Excel IF Formula With Multiple Conditions (With Examples)
- How to Use SUMIF in Excel (With Examples)
- Percentage Change Between Two Numbers in Excel
- How to Concatenate Strings in Excel (With Examples)
- How to Delete a Git Branch (Local and Remote)
More Excel questions · All questions · Just Fucking Prompt It