#N/A in Excel: what it means and the six causes
#N/A is a lookup saying it found nothing. The six things that cause it, how to test for each, and why IFNA is the right wrapper and IFERROR is not.
#N/A means not available. A lookup went to find
something and it was not there. That is all it means.
So the first thing to know is what it is not. It is not a broken formula. It is not a bug in Excel. It is your formula telling you the truth about your data. The fix is almost never to hide the message. The fix is to find out why the match failed.
Put this table in A1 to D5. Every example below uses it.
| Row | A | B | C | D |
|---|---|---|---|---|
| 1 | ID | Name | Team | Hours |
| 2 | E100 | Ana | Sales | 38 |
| 3 | E200 | Bo | Sales | 41 |
| 4 | E300 | Cem | Support | 35 |
| 5 | E400 | Dita | Support | 40 |
The six causes, most common first
1. A stray space
You look up "E200 " and the table holds
"E200". One trailing space. They are not the same text, so
there is no match.
You cannot see this. A space at the end of a cell looks like nothing at all. That is what makes it the first thing to check.
Test it: =LEN(F1) next to your lookup value.
If it says 5 and the code is 4 characters, there is a space.
Fix it: =VLOOKUP(TRIM(F1),A2:D5,4,FALSE).
TRIM removes spaces at both ends and collapses doubles in the middle.
2. A number stored as text
Type 200 in one cell. Paste 200 from a website into another. They look identical. One is a number and one is text, and a lookup never matches them.
Excel shows a small green triangle in the corner of a number stored as text. It is easy to miss. A faster test is alignment. A real number sits on the right of its cell. Text sits on the left.
Test it: =ISNUMBER(F1). FALSE on something
that looks like a number is your answer.
Fix it: turn one side into the other.
=VLOOKUP(VALUE(F1),A2:D5,4,FALSE) makes the lookup value a
number. =VLOOKUP(TEXT(F1,"0"),A2:D5,4,FALSE) makes it text.
Which one depends on what is in the table.
3. The code is not in the first column of the table
VLOOKUP only searches the first column of the range you give it. Not column A of the sheet. The first column of your range.
=VLOOKUP("Sales",A2:D5,4,FALSE) returns #N/A. Sales is in column
C. The range starts at A. So VLOOKUP looks for Sales in the ID column, and it is not there.
Fix it: start the range where the thing you are searching for lives.
=VLOOKUP("Sales",C2:D5,2,FALSE) returns 41, the first Sales
row.
Or use XLOOKUP. It takes the search column and the return column separately. The order does not matter.
4. An approximate match on an unsorted table
The fourth argument of VLOOKUP is the one people leave out. Left out, it means TRUE. TRUE means approximate.
An approximate match assumes the first column is sorted from small to large. On an unsorted table it returns the wrong row, or #N/A. There is no warning either way.
Fix it: write FALSE. Every time.
=VLOOKUP("E300",A2:D5,4,FALSE).
TRUE has one real use: grade bands, tax bands, shipping tiers. Anything where you want "the last row at or below this number". Use it on purpose, on a sorted table, and nowhere else.
5. The range moved when you copied the formula down
Write =VLOOKUP(F1,A2:D5,4,FALSE) in G1 and drag it down. In G2
the range has become A3:D6. In G3 it is A4:D7. The table is sliding off the bottom of itself.
The first rows work. The last rows return #N/A. That pattern is how you know.
Fix it: lock the range with dollar signs.
=VLOOKUP(F1,$A$2:$D$5,4,FALSE). Press F4 with the range
selected to add them.
6. There is genuinely no match
Sometimes the code really is not in the table. E500 does not exist. #N/A is then the correct answer and the formula is working.
This is the only cause where hiding the message is the right thing to do.
Hiding it: IFNA, not IFERROR
These two look interchangeable. They are not, and the difference matters.
| Wrapper | Catches | Use when |
|---|---|---|
| IFNA | #N/A only | A lookup that is allowed to find nothing |
| IFERROR | Every error there is | Almost never, on a lookup |
=IFERROR(VLOOKUP(F1,A2:D5,9,FALSE),"Not found") says "Not found".
But there is no ninth column. That is a #REF!, a real mistake in
the formula, and IFERROR has swallowed it. The sheet now reports "Not found" for every row and
looks fine.
=IFNA(VLOOKUP(F1,A2:D5,9,FALSE),"Not found") shows the #REF! and
lets you fix it. Use IFNA on lookups. Keep IFERROR for the places where any error at all is
worth replacing.
XLOOKUP has it built in
XLOOKUP takes a fourth argument for exactly this:
=XLOOKUP(F1,A2:A5,D2:D5,"Not found").
No wrapper. It is also exact by default, so cause 4 cannot happen. It takes two separate ranges, so cause 3 cannot happen either. If your Excel has XLOOKUP, two of the six causes go away on their own.
One case where #N/A is what you want
A chart skips #N/A. It draws a gap instead of a zero. A missing month plotted as 0 is a line that drops to the floor and tells a lie.
=NA() puts a deliberate #N/A in a cell for this. It is the one
time you type the error yourself.
Questions people ask
Why does my VLOOKUP work on some rows and not others?
Look at which rows fail. The last ones failing means the range is not locked, cause 5. Rows
scattered through the list means the data is inconsistent, cause 1 or 2. Check with
=LEN() and =ISNUMBER() on a row
that fails.
Is #N/A the same as an empty cell?
No. An empty cell has nothing in it. #N/A is a value, and it spreads. Any formula that reads a cell holding #N/A returns #N/A too. That is why one bad lookup can turn a whole column red.
Can I make #N/A print as blank?
=IFNA(VLOOKUP(F1,A2:D5,4,FALSE),"") gives an empty text string.
It looks empty and it is not: =ISBLANK() on it says FALSE, and
COUNT will not count it. Use it for a report somebody reads, not for a column something else
calculates from.
Why does MATCH return #N/A when I can see the value?
Same six causes. MATCH is a lookup. Check the third argument first. 0 means exact. Leaving it out means 1, which is approximate on sorted data.