GridDojo

How to Fix the #N/A Error in Excel

The #N/A error means a lookup could not find what it was searching for — VLOOKUP, XLOOKUP, or MATCH came up empty. The fix is to check the lookup value for typos, spaces, and type mismatches, or handle it cleanly with IFNA. Here is how.

By The GridDojo Team Published 2 min read

#N/A stands for "not available", and it means a lookup could not find what you asked for. It shows up most often with VLOOKUP, XLOOKUP, MATCH, and INDEX MATCH — the function searched, found no match, and reported back honestly. The good news: the cause is almost always small and easy to fix.

What causes #N/A

  • The lookup value genuinely is not in the list you are searching.
  • A trailing or leading space makes "North " and "North" look different to Excel.
  • A number is stored as text in one place and as a real number in the other, so they never match.
  • VLOOKUP is missing its final FALSE, so it does an approximate match on unsorted data.
  • A small typo in the lookup value or the search range.

Step 1: Confirm the value really exists

First, check that what you are searching for is actually in the lookup column. Use a quick test next to your data — =COUNTIF(A2:A100, D1) returns how many times the value in D1 appears. If it returns 0, the value is not there (or not there in the form you typed it).

Step 2: Clean the mismatch

  1. Strip stray spaces with TRIM, like =TRIM(D1), or clean the whole column with Find & Replace.
  2. Make the types match: if the list holds real numbers, the lookup value must be a number too, not text. Data > Text to Columns > Finish quickly converts text-numbers.
  3. For VLOOKUP, add FALSE as the last argument to force an exact match: =VLOOKUP(D1, A2:B100, 2, FALSE).

Step 3: Handle it cleanly with IFNA

Sometimes a #N/A is expected — the value simply is not there yet. Instead of an ugly error, show a friendly message with IFNA, which catches only #N/A and leaves other errors visible:

=IFNA(VLOOKUP(D1, A2:B100, 2, FALSE), "Not found")

IFNA is better than IFERROR here because it hides only the not-found case, so a real mistake elsewhere still shows up. This error is most common with the lookup family — VLOOKUP, XLOOKUP, and INDEX MATCH. XLOOKUP even has a built-in not-found argument, so it can skip IFNA entirely.

Frequently asked questions

Why does my VLOOKUP return #N/A when the value is clearly there?

Almost always a hidden space or a type mismatch. The value may have a trailing space, or it is stored as text in one place and as a number in the other. Wrap the lookup value in TRIM and make sure both sides are the same data type.

What is the difference between IFNA and IFERROR?

IFNA catches only the #N/A error, while IFERROR catches every error type. Use IFNA for lookups so that a genuine problem elsewhere — like a #REF! or #VALUE! — still shows up instead of being hidden.

How do I remove #N/A from a whole column?

Wrap each lookup in IFNA to replace #N/A with a blank or a message, like =IFNA(VLOOKUP(...), ""). Fix the underlying data where you can, and use IFNA only to handle the cases that are genuinely expected to be missing.

Sources and references

  1. Microsoft Support — How to correct a #N/A error