Fix the #N/A Error: 5 Causes and Their Fixes

#N/A means "not available" — the lookup ran and found nothing. Unlike most Excel errors it is often the correct answer, so the first question is never "how do I hide it" but "is it telling the truth".

Three formulas answer that in under a minute:

=COUNTIF(lookup_range, A2)     is it there at all?
=LEN(A2)                       is it the length you expect?
=ISNUMBER(A2)                  is it the type you expect?

Three identical-looking errors with three different causes. The two diagnostic columns separate them in seconds.

Read the diagnostics like this

What you see	Cause
In list? 0, length normal	It genuinely isn't there
In list? 0, length one or two too long	Hidden spaces
Is number? differs between the two sides	Text against number
Some rows work, rows further down don't	The range wasn't locked

Cause 1: it really isn't there

The most common cause, and usually not a fault. A code that isn't in the reference table is information — a new product, a typo at source, a record that hasn't been set up yet.

Count them before doing anything else:

=COUNTIF($B$2:$B$500, "#N/A")

That won't work — #N/A isn't text. Use this instead:

=SUMPRODUCT(--ISNA($B$2:$B$500))

COUNTIF can't count errors; ISNA tests each cell and SUMPRODUCT adds the TRUEs. Knowing you have four unmatched rows out of five hundred is a different situation from having four hundred, and the fix is different in each case.

Cause 2: spaces you can't see

The value is in the table, you can read it on screen, and the lookup still fails. Compare the lengths:

=LEN(A2)      →  9
=LEN(H4)      →  8

One character longer than it should be is a trailing space almost every time.

TRIM removes ordinary spaces. It does not remove non-breaking spaces, which arrive with anything copied from a web page or a PDF and look identical:

=TRIM(SUBSTITUTE(A2, CHAR(160), " "))

⚠️ Fix the data, not the formula. Wrapping TRIM around the lookup value patches one symptom and leaves the same dirty value breaking every other formula that touches that column. Clean the column once, in place.

Cause 3: text against number

A reference number typed by hand is a number. The same reference imported from a system is often text. They look identical and Excel's exact-match lookups treat them as different values.

=ISNUMBER() on both sides tells you immediately. TRUE on one and FALSE on the other is your answer.

Fix whichever side is wrong: select the column, Data → Text to Columns → Finish, which re-parses every cell in place. It looks like it does nothing and is the fastest fix there is.

Worth knowing: spreadsheet programs disagree here. Excel treats text "4405" and the number 4405 as different; some others quietly convert. A file that works in one and fails in the other, with no other change, is usually this.

Cause 4: the range moved

The signature is unmistakable — the top rows work and rows further down fail.

Wrong:  =INDEX(I2:I50, MATCH(A2, H2:H50, 0))
Right:  =INDEX($I$2:$I$50, MATCH(A2, $H$2:$H$50, 0))

Without the dollar signs, copying down row by row drags the search range down with it. By row 30 it's looking at H30:H78 and the earlier entries have fallen off the top.

Click into the reference and press F4 to lock it.

The VLOOKUP-only version

VLOOKUP only ever searches the first column of the range you give it. If your codes are in column B and you hand it A:D, it searches column A, finds nothing, and returns #N/A for every row.

Either start the range at the code column, or use INDEX MATCH, which has no such rule.

Cause 5: the missing zero

=MATCH(A2, $H$2:$H$50)        no final argument
=VLOOKUP(A2, $H$2:$I$50, 2)   no final argument

Leave the last argument off and both functions assume your list is sorted ascending and go looking for the nearest value at or below what you asked for.

On an unsorted list that produces one of two outcomes. Either #N/A, or — far worse — a real value from the wrong row, with nothing to indicate anything went wrong.

Always write the 0 in MATCH and FALSE in VLOOKUP. This is the one cause on the list where the #N/A is doing you a favour, because the alternative failure is silent.

IFNA, not IFERROR

Once the errors are understood rather than merely present, you may want to tidy them. Use the right wrapper:

Function	Catches
IFNA(x, "")	#N/A only
IFERROR(x, "")	#N/A, #REF!, #VALUE!, #DIV/0!, #NAME?, #NUM!, #NULL!

IFERROR is the one everybody knows and it hides too much. It will swallow a #REF! from a deleted column and a #NAME? from a mistyped function name — genuine breakage, silenced and made to look like a normal empty cell.

IFNA hides the expected miss and lets everything else surface. It's available from Excel 2013 onwards and it should be your default in any lookup.

And return something that means something:

=IFNA(INDEX(...), "Code not found")

A blank looks like a row nobody has filled in yet. Words tell you the lookup ran and came back empty-handed — a distinction that matters when a total silently excludes those rows, as the price lookup guide shows.

Deliberate #N/A

Occasionally you want it. =NA() returns #N/A on purpose, and it has one real use: charts skip #N/A points, where they plot a zero as an actual zero on the axis.

For a monthly chart where later months haven't happened yet, NA() stops the line diving to the floor.

Does this work in Google Sheets?

Yes. COUNTIF, LEN, ISNUMBER, ISNA, IFNA, IFERROR, TRIM and SUBSTITUTE all behave the same, and MATCH needs its 0 just as much.

Sheets is more forgiving about text-versus-number in lookups, which is why a sheet converted from Excel sometimes stops showing errors it should still be showing.

The short version

• #N/A means the lookup found nothing. Check whether that's true before hiding it.
• Three diagnostics: COUNTIF, LEN, ISNUMBER.
• Count the errors with SUMPRODUCT(--ISNA(range)). COUNTIF can't.
• One character too long means a hidden space. TRIM misses non-breaking ones.
• Text against number looks identical and never matches. Text to Columns → Finish.
• Top rows fine, lower rows failing means an unlocked range. F4.
• VLOOKUP only searches the first column of its range.
• Always write the 0 or FALSE. Without it, wrong answers replace errors.
• Use IFNA, not IFERROR, and return words rather than blanks.

Next steps: the three data-level causes here are the same ones that stop two lists matching, and finding them across a whole file is step two of cleaning an export. If the codes themselves have been mangled into dates, that's a different problem with a worse prognosis.