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

  1. 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.
  2. 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.
  3. Match data types. The number 1001 and the text "1001" do not match. Signs: left-aligned numbers, a green corner triangle, or ISNUMBER returning FALSE. Convert text to numbers with VALUE(A2), or multiply by 1 (A2*1), or convert the other side to text with A2&"".
  4. Use exact match. In VLOOKUP the 4th argument must be FALSE (or 0). Leaving it out or using TRUE on unsorted data causes wrong results or #N/A.
  5. 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, use XLOOKUP or INDEX/MATCH.
  6. Lock the range with `$` when copying the formula down. E2:F100 becomes E3:F101, then the value slides out of the range. Use $E$2:$F$100.
  7. 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:

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

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.

Watch the AI answer it →

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

More Excel questions · All questions · Just Fucking Prompt It