Excel errors / #N/A
#N/A error in Excel
A lookup could not find the value it was given.
#N/A means "not available". A formula went looking for a value and did not find it. It comes almost always from a lookup: VLOOKUP, XLOOKUP, HLOOKUP or MATCH.
The formula itself is usually fine. What is wrong is the match: the value is missing from the list, or it is there but not quite the same as far as Excel is concerned. A space at the end, or a number stored as text, is enough.
Have a formula that shows #N/A? Paste it into the formula checker with a few rows of your data. It points at the part that fails and says why.
5 causes of #N/A, and how to fix each
The value is not in the list
The simplest case: the code, name or ID you are looking up has no row in the table. Often that is correct, such as a new product that has no price yet.
A B C D 1 Code Price 2 P-1 3.5 #N/A 3 P-2 1.2 In D2:
=VLOOKUP("P-9", A2:B3, 2, FALSE)→ #N/AHow to fix it. Add the missing row if it should be there. If a missing value is normal, wrap the lookup in IFNA and say what to show instead.
A B C D 1 Code Price 2 P-1 3.5 Not found 3 P-2 1.2 In D2:
=IFNA(VLOOKUP("P-9", A2:B3, 2, FALSE), "Not found")→ Not foundA space you cannot see
Data copied from another system often carries a space at the end. "P-1 " and "P-1" look the same in the cell and are different to Excel. In the sheet below, A2 has such a space.
A B C D 1 Code Price 2 P-1 3.5 #N/A 3 P-2 1.2 In D2:
=VLOOKUP("P-1", A2:B3, 2, FALSE)→ #N/AHow to fix it. Remove the spaces in the data (a helper column with TRIM, then paste it back as values). Or clean the column inside the formula: XLOOKUP accepts
TRIM(A2:A3)as the range to search.A B C D 1 Code Price 2 P-1 3.5 3.5 3 P-2 1.2 In D2:
=XLOOKUP("P-1", TRIM(A2:A3), B2:B3)→ 3.5A number on one side, text on the other
The ID 1002 typed as a number does not match "1002" stored as text, which is common in exported files. Numbers stored as text sit on the left of the cell, and often carry a small green triangle.
A B C D 1 ID Name 2 '1001 Ana #N/A 3 '1002 Ben In D2:
=VLOOKUP(1002, A2:B3, 2, FALSE)→ #N/AHow to fix it. Make both sides the same kind. To turn a whole column of text into numbers, select it and use Data > Text to Columns > Finish. Or convert the value you look up:
TEXT(1002, "0")makes it text.A B C D 1 ID Name 2 '1001 Ana Ben 3 '1002 Ben In D2:
=VLOOKUP(TEXT(1002, "0"), A2:B3, 2, FALSE)→ BenVLOOKUP is searching the wrong column
VLOOKUP only looks in the first column of the table you give it. If the value you search for is in the second column, it is never found, even though you can see it.
A B C D 1 Name Code 2 Ana P-1 #N/A 3 Ben P-2 In D2:
=VLOOKUP("P-2", A2:B3, 1, FALSE)→ #N/AHow to fix it. Use XLOOKUP, which takes the column to search and the column to return separately, or INDEX with MATCH.
A B C D 1 Name Code 2 Ana P-1 Ben 3 Ben P-2 In D2:
=XLOOKUP("P-2", B2:B3, A2:A3)→ BenThe table moved when the formula was filled down
A lookup range written as
A2:B50shifts toA3:B51, thenA4:B52, as the formula is copied down. Rows further down search a table that no longer includes the top rows, so values near the top stop being found.How to fix it. Lock the range with $ signs before filling down:
$A$2:$B$50. Select the range in the formula and press F4.
Hiding it on purpose
To show your own text in place of #N/A, use IFNA: =IFNA(VLOOKUP(E2, A2:B50, 2, FALSE), "Not found"). IFERROR works too, but it also hides every other error, including a misspelled function name. XLOOKUP has this built in as its fourth argument.
Common questions
How do I fix #N/A in VLOOKUP?
Check, in this order: that the value really is in the first column of the table; that neither side has extra spaces; that both are numbers or both are text; that the formula ends with FALSE for an exact match; and that the table range is locked with $ signs.
Why does VLOOKUP return #N/A when the value exists?
Because to Excel it is not the same value. The usual reasons are a space at the end of one of them, or a number in one place and the same digits stored as text in the other. =A2=E2 in an empty cell tells you: FALSE means they differ.
How do I replace #N/A with 0 or a blank?
Wrap the formula in IFNA: =IFNA(VLOOKUP(E2, A2:B50, 2, FALSE), 0) for zero, or "" in place of the 0 for an empty-looking cell.
Practise it
Functions involved
- VLOOKUP: Finds a value in the first column of a table and returns a value from the same row.
- XLOOKUP: Searches a range and returns the matching item from another range. The modern replacement for VLOOKUP.
- MATCH: The position of a value in a row or column.
- IFNA: Returns a fallback value only for #N/A errors.
- ISNA: TRUE if the value is the #N/A error.
- NA: Returns the #N/A error on purpose.
- TRIM: Removes extra spaces, leaving single spaces between words.
Other errors
- #VALUE!: The formula was given the wrong kind of value, such as text where it needs a number.
- #REF!: The formula refers to a cell that no longer exists.
- #DIV/0!: The formula divides by zero or by an empty cell.
- #NAME?: Excel does not recognise a word in the formula.
- #NUM!: The calculation has no valid number as its answer.
- #SPILL!: A formula that returns several values has no room to put them.
- #CALC!: A formula that returns a list ended up with nothing to return.
- #NULL!: Two ranges that were expected to cross do not.
- #####: The column is too narrow to show the number, or a date is negative.
- Circular reference: A formula depends on its own result.