IFNA: Catch Only #N/A Errors

Excel Formulas › Logical

2013+IFNA

IFNA handles a missing lookup (#N/A) while letting real errors surface — safer than IFERROR, which hides every error including genuine bugs.

Quick formula: default a lookup that finds nothing:
=IFNA(VLOOKUP(A2, table, 2, FALSE), "Not found")
Only #N/A triggers the default; a #REF! or #VALUE! still shows, so you notice broken formulas.

Functions used (tap for the full reference guide):

IFNAIFERROR

The example

A missing key returns a message, not an error.

AB
1LookupResult
2FoundValue
3MissingNot found

The formula

Default on #N/A only:

=IFNA(VLOOKUP(A2, table, 2, FALSE), "Not found") // real errors still surface

How it works

IFNA is the precise version of IFERROR:

  1. IFNA(value, default) returns the default only when value is #N/A.
  2. Any other error (#REF!, #VALUE!, #DIV/0!) passes through, so you see it and can fix it.
  3. This is the right wrapper for lookups, where #N/A means “not found” but other errors mean “something’s wrong.”
  4. IFERROR catches everything — convenient, but it can mask genuine bugs.

Prefer IFNA over IFERROR for lookups. IFERROR around a VLOOKUP will also swallow a #REF! from a deleted column, hiding the breakage behind your “Not found” text. IFNA only masks the expected miss.

Try it: interactive demo

Live demo

Look up a key (try a missing one).

Result:

Variations

XLOOKUP default

Built-in (365):

=XLOOKUP(A2, ids, vals, "Not found")

IFERROR (catches all)

When you want every error hidden:

=IFERROR(VLOOKUP(...), "")

Only #N/A test

Detect it:

=ISNA(VLOOKUP(...))

Pitfalls & errors

2013 or later. IFNA isn’t in Excel 2007.

IFNA only catches #N/A. If you also need to handle other errors, that’s a deliberate choice — combine with IFERROR only where appropriate.

XLOOKUP has it built in. Its 4th argument is essentially IFNA — no wrapper needed.

Practice workbook

📊
Download the free IFNA: Catch Only #N/A Errors practice workbook
An IFNA sheet with the XLOOKUP, IFERROR, and ISNA variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

What's the difference between IFNA and IFERROR in Excel?
IFNA catches only #N/A; IFERROR catches every error. For lookups, IFNA is safer because real errors like #REF! still surface.
Why prefer IFNA for VLOOKUP?
A missing key returns #N/A (expected), but a deleted column returns #REF! (a bug). IFNA handles the miss while letting the bug show; IFERROR hides both.
Does XLOOKUP need IFNA?
No — its fourth argument provides the if-not-found default directly.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Trap errors with IFERROR · XLOOKUP if not found · Highlight errors

Function references: IFNA · IFERROR