Logical · Error handling

IFERROR vs IFNA

Both wrap a formula and supply a fallback when it errors — but they catch different things. IFERROR catches every error type (#N/A, #DIV/0!, #VALUE!, #REF!, #NUM!, #NAME?, #NULL!). IFNA catches only #N/A and lets every other error propagate. That makes IFNA the surgical choice for lookups (where #N/A means 'not found' but a #REF! means a real bug you want to see). Every claim below is measured in the HyperFormula engine on identical formulas.

fx
IFERROR(…)  vs  IFNA(…)
7
measured scenarios
7
error types IFERROR catches
1
error type IFNA catches
0
external dependencies
▶ Try both live See the feature matrix
🏆Verdict: IFNA for lookups (surgical); IFERROR when any error should fall back (broad).

IFERROR(value, value_if_error) returns the fallback for ANY error; IFNA(value, value_if_na) returns the fallback ONLY for #N/A and re-throws everything else. Measured: 1/0 wrapped in IFERROR gives "err", but wrapped in IFNA still returns #DIV/0!. The same split holds for #VALUE! (VALUE("abc")), an out-of-range INDEX (#NUM! in this engine, #REF! in Excel) and #NUM! (SQRT(-1)). For a VLOOKUP/XLOOKUP where #N/A legitimately means 'not found', IFNA is safer: it hides the expected miss but still surfaces a genuine #REF! or #VALUE! bug instead of masking it. Use IFERROR only when you truly want every failure mode to collapse to one fallback. IFNA is Excel 2013+; IFERROR is 2007+.

IFERROR strengths

  • Catches every error type — one net for all failures
  • Available since Excel 2007 (universal)
  • Simplest when any error should show a fallback
  • Risk: masks real bugs (#REF!, #VALUE!) as a bland fallback

IFNA strengths

  • Catches only #N/A — lets real errors surface
  • Ideal for lookups: 'not found' vs 'broken formula' stay distinct
  • Won't hide a #REF! or #DIV/0! bug behind a fallback
  • Excel 2013+ only (not in 2007/2010)

Feature-by-feature matrix

Eight dimensions, measured. They tie on #N/A and on success; they diverge on every other error type — IFERROR catches them, IFNA re-throws them. That divergence is the entire decision.

CapabilityIFERRORIFNAWhy it matters
Arguments✓ (value, value_if_error)✓ (value, value_if_na)Same shape; second arg is the fallback.
Catches #N/A✓ yes✓ yesBoth fall back on a missing lookup.
Catches #DIV/0!✓ yes → fallback✗ no → #DIV/0!Measured: 1/0 → IFERROR="err", IFNA=#DIV/0!.
Catches #VALUE!✓ yes → fallback✗ no → #VALUE!VALUE("abc"): IFERROR catches, IFNA passes.
Catches #REF! / #NUM!✓ yes → fallback✗ no → errorINDEX out of range / SQRT(-1): only IFERROR catches.
No error✓ returns value✓ returns valueBoth pass a successful result through unchanged.
Masks real bugs◐ yes — hides all errors✓ no — only hides #N/AIFNA keeps genuine bugs visible; IFERROR can mask them.
Excel version✓ 2007+ (all)◐ 2013+ onlyIFNA needs Excel 2013+; IFERROR works everywhere.

Syntax, side by side

Same two-argument shape. The difference is which errors trigger the fallback: all of them (IFERROR) vs only #N/A (IFNA).

IFERROR

=IFERROR(value, value_if_error)
valueThe formula to evaluate and watch for ANY error.
value_if_errorReturned if value produces any error (#N/A, #DIV/0!, #VALUE!, #REF!, #NUM!, #NAME?, #NULL!).
=IFERROR(VLOOKUP(999,A2:C7,3,FALSE),"missing")

IFNA

=IFNA(value, value_if_na)
valueThe formula to evaluate and watch for #N/A specifically.
value_if_naReturned ONLY if value produces #N/A. Every other error propagates unchanged.
=IFNA(VLOOKUP(999,A2:C7,3,FALSE),"missing")

Live head-to-head playground

One editable lookup table, two wrappers. Try a missing key (both fall back) then a real error like 1/0 (only IFERROR falls back) — both sides re-evaluate in the same engine.

A1Sample data — click any cell to edit; both formulas re-evaluate live

IFERROR

—

IFNA

—
HyperFormula 3.5 · live

Every difference, measured

These are not feature-list claims. Each pair below was evaluated on the identical dataset in the HyperFormula 3.5 engine, and the outputs are baked from that run. Reproduce them in the playground above.

Catch a missing lookup (#N/A)

VLOOKUP a key that doesn't exist; both should fall back.
IFERROR
=IFERROR(VLOOKUP(999,A2:C7,3,FALSE),"missing")
missing
IFNA
=IFNA(VLOOKUP(999,A2:C7,3,FALSE),"missing")
missing
Both catch #N/A: VLOOKUP(999,...) misses, so IFERROR and IFNA both return "missing". This is the one error type they share — the expected 'not found' case.

Division by zero (#DIV/0!)

Wrap 1/0 — does each catch it?
IFERROR
=IFERROR(1/0,"err")
err
IFNA
=IFNA(1/0,"err")
#DIV/0!
The split: IFERROR(1/0,"err") = "err" (catches #DIV/0!), but IFNA(1/0,"err") = #DIV/0! (passes it through). IFNA only handles #N/A, not arithmetic errors.

Bad coercion (#VALUE!)

Wrap VALUE("abc") — does each catch it?
IFERROR
=IFERROR(VALUE("abc"),"err")
err
IFNA
=IFNA(VALUE("abc"),"err")
#VALUE!
IFERROR(VALUE("abc"),"err") = "err" (catches #VALUE!), IFNA(VALUE("abc"),"err") = #VALUE! (passes). A coercion failure is not #N/A, so IFNA leaves it visible.

Out-of-range INDEX (#NUM! in HF)

Wrap INDEX(A2:A7,99) — does each catch it?
IFERROR
=IFERROR(INDEX(A2:A7,99),"err")
err
IFNA
=IFNA(INDEX(A2:A7,99),"err")
#NUM!
IFERROR(INDEX(A2:A7,99),"err") = "err" (catches the error), IFNA(INDEX(A2:A7,99),"err") = #NUM! (passes it through). HyperFormula reports an out-of-range INDEX as #NUM! (real Excel uses #REF!); either way it is a real bug IFNA refuses to hide.

No error — value passes through

VLOOKUP a key that exists; both return the price.
IFERROR
=IFERROR(VLOOKUP(102,A2:C7,3,FALSE),"missing")
19.5
IFNA
=IFNA(VLOOKUP(102,A2:C7,3,FALSE),"missing")
19.5
No error: VLOOKUP(102,...) finds Gadget at 19.5. Both IFERROR and IFNA return 19.5 unchanged — the fallback only triggers on error.

Bad number (#NUM!)

Wrap SQRT(-1) — does each catch it?
IFERROR
=IFERROR(SQRT(-1),"err")
err
IFNA
=IFNA(SQRT(-1),"err")
#NUM!
IFERROR(SQRT(-1),"err") = "err" (catches #NUM!), IFNA(SQRT(-1),"err") = #NUM! (passes). A domain error is not #N/A.

Nesting order

Compose the two around 1/0; the outer IFERROR catches what IFNA passes.
IFERROR
=IFERROR(IFNA(1/0,"na"),"div")
div
IFNA
=IFNA(IFERROR(1/0,"div"),"na")
div
Both orders around 1/0 end at "div": the inner IFNA passes #DIV/0! through, and the outer IFERROR catches it. Nesting lets you give #N/A its own message while a broad net handles the rest.

Engine benchmark

Relative engine cost of wrapping the same stream of lookups. The wrapper overhead is tiny and identical; choose by error semantics, not speed.

Table sizeIFERRORIFNA
1,000 rows × 200 lookups302.35 ms269.76 ms
5,000 rows × 200 lookups5039.73 ms5125.44 ms
10,000 rows × 200 lookups19929 ms19847.63 ms
Measured in the HyperFormula 3.5 engine on this page, not in Microsoft Excel. Absolute times are engine-specific; use only as a relative signal. Real Excel performance depends on version, data layout and calculation chain.

Which one should you use?

Choose by whether you want to hide every error or only the 'not found' case.

SituationUseWhy
VLOOKUP/XLOOKUP 'not found' fallbackIFNAIFNA hides the expected #N/A but still surfaces a real #REF! bug.
Any error should show a defaultIFERRORIFERROR collapses every failure mode to one fallback.
You must not mask formula bugsIFNAIFNA re-throws #DIV/0!, #VALUE!, #REF! so you see them.
Workbook must open in Excel 2007/2010IFERRORIFNA doesn't exist before 2013; IFERROR works everywhere.
Division that may legitimately be 0/0IFERRORIFERROR catches #DIV/0!; IFNA would let it through.
Clean lookup column for a reportIFNAIFNA gives 'missing' for absent keys without hiding breakage.
Nested error handlingEitherCompose them: IFNA inner for #N/A, IFERROR outer for the rest.
Successful formulaEitherBoth pass the value through unchanged when there's no error.

IFERROR ↔ IFNA migration

Swap is a one-word edit, but it changes which errors you hide. Moving IFERROR→IFNA un-masks real bugs; moving IFNA→IFERROR hides them.

TaskIFERROR / old wayIFNAWhat changed
Lookup fallback=IFERROR(VLOOKUP(k,A2:C7,3,FALSE),"missing")=IFNA(VLOOKUP(k,A2:C7,3,FALSE),"missing")Same on #N/A; IFNA also keeps real errors visible.
Safe division=IFERROR(A2/B2,0)=IFNA(A2/B2,0)IFERROR catches #DIV/0!; IFNA does NOT — it passes through.
Coercion guard=IFERROR(VALUE(A2),0)=IFNA(VALUE(A2),0)IFERROR catches #VALUE!; IFNA passes it through.
Layered handling=IFERROR(IFNA(f,"not found"),"other error")=IFNA(IFERROR(f,"x"),"y")Inner IFNA handles #N/A; outer IFERROR catches the rest.
Legacy compatibility=IFERROR(f,0)=IFNA(f,0)IFNA needs 2013+; keep IFERROR for 2007/2010 workbooks.

Frequently asked questions

What is the difference between IFERROR and IFNA?
Scope of errors caught. IFERROR returns its fallback for ANY error (#N/A, #DIV/0!, #VALUE!, #REF!, #NUM!, #NAME?, #NULL!). IFNA returns its fallback ONLY for #N/A and lets every other error propagate. Measured: 1/0 wrapped in IFERROR gives the fallback, but wrapped in IFNA still returns #DIV/0!.
Which should I use for VLOOKUP / XLOOKUP?
Usually IFNA. A missing key legitimately produces #N/A ('not found'), which you want to replace with a friendly default. But a #REF! (broken range) or #VALUE! (wrong type) is a real bug you want to SEE, not hide. IFNA hides the expected miss while surfacing genuine bugs; IFERROR would mask both behind the same fallback.
Does IFNA catch division by zero?
No — measured. IFNA(1/0,"err") returns #DIV/0!, because #DIV/0! is not #N/A. IFERROR(1/0,"err") returns "err". If you need to guard against #DIV/0!, use IFERROR (or test the denominator explicitly).
Is IFERROR bad practice?
It can be. Because IFERROR hides every error, it can mask a genuine bug (#REF!, #VALUE!, #NAME?) as a bland fallback, making a broken formula look fine. Use it when you truly want all failures to collapse to one value; prefer IFNA when only #N/A is expected and other errors should remain visible for debugging.
What Excel versions support each?
IFERROR is available in Excel 2007 and later (universal). IFNA was introduced in Excel 2013, so it is absent from 2007/2010. If a workbook must open in those older versions, use IFERROR. Both work in the HyperFormula engine on this page.
Can I nest them?
Yes. A common pattern is IFERROR(IFNA(formula,"not found"),"other error"): the inner IFNA handles the expected #N/A with a specific message, and the outer IFERROR catches any other error with a different message. Measured: both nesting orders around 1/0 ultimately let the outer IFERROR catch the #DIV/0! that IFNA passed through.