Excel IFNA: Catch Lookup Misses Only
By Szabó Gergő · Updated
IFNA handles only #N/A. Prefer it over IFERROR on lookups so other errors stay visible.
Syntax and arguments
=IFNA(value, value_if_na)- value
- Usually an XLOOKUP, VLOOKUP, or MATCH.
- value_if_na
- The fallback when nothing matches.
IFNA examples
01
Friendly miss
XLOOKUP in G2.
=IFNA(XLOOKUP(G2,A2:A100,D2:D100),"Not found")A missing code shows Not found. A broken range still shows #REF!.
02
Blank on miss
Dashboards that should stay quiet.
=IFNA(VLOOKUP(G2,A2:D100,4,FALSE),"")A blank is easier to chart than #N/A.
03
Nested lookup
Try one table, then another.
=IFNA(XLOOKUP(G2,A2:A100,D2:D100),XLOOKUP(G2,F2:F50,H2:H50))The second lookup runs only when the first misses.
Common mistakes
Using IFERROR on a whole model
IFERROR hides #DIV/0! and #VALUE!. Use IFNA on lookups.
Returning 0 for a missing ID
0 can look like a real amount. Prefer text or a blank.
Wrapping a math formula
IFNA will not catch #VALUE!. Fix types instead.
IFNA FAQ
IFNA vs IFERROR?
IFNA is narrower and safer for lookups. IFERROR catches every error type.
Does XLOOKUP need IFNA?
Not if you pass if_not_found. IFNA is useful around VLOOKUP and MATCH.
Can I nest IFNA?
Yes, to try several lookup tables in order.