exceltestpracticeStart free
Practice testsLearnFunctionsShortcutsPricingStart free

Excel errors

#N/A error in Excel

The lookup came back empty-handed: Excel searched for a value and it is not there.

What #N/A means

#N/A stands for “not available”. Excel uses it for one situation above all others: a function looked for something and the thing was not there. It is the only error value that is sometimes the right answer, because “no match” is a legitimate result for a lookup.

The error is returned by the lookup family (VLOOKUP, HLOOKUP, MATCH, XLOOKUP and LOOKUP), by some dynamic array functions when two arrays do not line up, and by the NA() function, which exists to return it on purpose. Charts treat #N/A as a gap rather than a zero, which is why analysts sometimes plant it deliberately in a data series.

The practical question is always the same: is the miss real or accidental? A genuine miss is a data fact you handle with IFNA. An accidental one is a mismatch between two things that look equal and are not, and the fix is to clean the data. The three causes below cover almost every accidental case.

Causes of #N/A and how to fix each

1. The lookup value is not in the list

This is the textbook case. VLOOKUP, HLOOKUP, MATCH and XLOOKUP in exact-match mode return #N/A when nothing in the search column equals the value you gave them. Here the staff list has no "Pam", so the lookup has nowhere to land.

Lookup for a name that is not listed. The formula =VLOOKUP(A5,A2:B4,2,FALSE) in cell C5 returns #N/A.
ABC
1NameDept
2AnaSales
3KimOps
4RajIT
5Pam#N/A

Fix. First decide whether the miss is a data problem or the right answer. If "Pam" should be in the list, add her row. If a miss is a normal outcome, such as an optional discount code, leave the error alone and trap it deliberately (see the trapping section below).

2. A stray space or a text-versus-number mismatch

The value looks identical on screen and still fails. A trailing space makes "Kim " a different string from "Kim". The same happens when one side is the number 1001 and the other is the text "1001", which is common after importing a CSV or pulling IDs out of text with LEFT or MID.

Lookup value with a trailing space. The formula =VLOOKUP(A5,A2:B4,2,FALSE) in cell C5 returns #N/A.
ABC
1NameDept
2AnaSales
3KimOps
4RajIT
5Kim #N/A

Fix. Clean the lookup value instead of retyping it: TRIM(A5) removes stray spaces, VALUE(A5) turns number-like text into a real number, and A5&"" goes the other way when the list holds text IDs.

The same lookup with TRIM. The formula =VLOOKUP(TRIM(A5),A2:B4,2,FALSE) in cell C5 returns Ops.
ABC
1NameDept
2AnaSales
3KimOps
4RajIT
5Kim Ops

TRIM strips the trailing space, so the lookup value is now "Kim" and row 3 matches.

3. The return column sits left of the lookup column

VLOOKUP always searches the leftmost column of the table you hand it. If the ID you know is in column B and the answer you want is in column A, selecting A:B makes VLOOKUP search the names and never find the ID.

Searching for an ID in the wrong column. The formula =VLOOKUP(B5,A2:B4,1,FALSE) in cell C5 returns #N/A.
ABC
1NameID
2Ana1001
3Kim1002
4Raj1003
51002#N/A

Fix. Use INDEX and MATCH, or XLOOKUP, which take the search column and the answer column separately and have no left-to-right rule.

INDEX and MATCH reading from the left. The formula =INDEX(A2:A4,MATCH(B5,B2:B4,0)) in cell C5 returns Kim.
ABC
1NameID
2Ana1001
3Kim1002
4Raj1003
51002Kim

MATCH finds 1002 in B2:B4 (position 2) and INDEX returns the second name in A2:A4.

Trapping #N/A in a formula

Wrap the lookup in IFNA, not IFERROR. IFNA catches only #N/A, so a typo in the formula that would produce #NAME? or #REF! still shows itself instead of being painted over with your friendly message.

Give the fallback a meaning the reader can act on, such as "Not found" or 0, and keep it consistent down the column.

A lookup that answers 'Not listed' when there is no match. The formula =IFNA(VLOOKUP(A5,A2:B4,2,FALSE),"Not listed") in cell C5 returns Not listed.
ABC
1NameDept
2AnaSales
3KimOps
4RajIT
5PamNot listed

Rows that match return the department. A missing name returns the text from the second argument.

#N/A on Excel job tests

A lookup task often has one deliberate miss in the data, and the instructions say what to show for it, usually "Not found" or 0. That is an IFNA task with a VLOOKUP, XLOOKUP or INDEX/MATCH inside.

The other common version gives you an ID column that is half text and half numbers, and a lookup that fails on the text rows. If your formula is right and some rows still return #N/A, check the data type before you check the formula.

  • VLOOKUPFinds a value in the first column of a table and returns a value from another column in the same row.
  • XLOOKUPFinds a value in one range and returns the matching item from another range.
  • MATCHReturns the position of a value within a row or column.
  • IFNAReturns a value you choose when a formula results in
  • INDEXReturns the value at a given row and column position in a range.
  • TRIMRemoves extra spaces from text, leaving single spaces between words.
  • #VALUE!A formula got a kind of value it cannot use, most often text where it needed a number.
  • #REF!A formula points at a cell that no longer exists, or at a position outside the range it was given.

#N/A questions

What does #N/A mean in Excel?
It means a value is not available. Lookup functions return it when they cannot find what you asked for, and the NA() function returns it on purpose.
Is #N/A a bad error?
Not always. It is the one error that can be the correct answer, because a missing item is a legitimate result. It becomes a bug when the item is really there and the match failed on spaces or data types.
How do I remove #N/A from a column of VLOOKUPs?
Wrap each lookup in IFNA(lookup, "text"). Use the fallback to say what the miss means, and do not use IFERROR unless you want every other error hidden as well.
Why does VLOOKUP return #N/A when the value is clearly there?
Usually a trailing space, a number stored as text, or a table range that starts in the wrong column. Test the match with =A5=A2 or =LEN(A5) to see the difference.

Practice working with errors

The free 5-item sampler uses the same kind of formulas in a working grid, and it shows you the error value when a formula goes wrong.

Try a 5-item Excel test sample