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.
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+.
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.
| Capability | IFERROR | IFNA | Why it matters |
|---|---|---|---|
| Arguments | ✓ (value, value_if_error) | ✓ (value, value_if_na) | Same shape; second arg is the fallback. |
| Catches #N/A | ✓ yes | ✓ yes | Both 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 → error | INDEX out of range / SQRT(-1): only IFERROR catches. |
| No error | ✓ returns value | ✓ returns value | Both pass a successful result through unchanged. |
| Masks real bugs | ◐ yes — hides all errors | ✓ no — only hides #N/A | IFNA keeps genuine bugs visible; IFERROR can mask them. |
| Excel version | ✓ 2007+ (all) | ◐ 2013+ only | IFNA needs Excel 2013+; IFERROR works everywhere. |
Same two-argument shape. The difference is which errors trigger the fallback: all of them (IFERROR) vs only #N/A (IFNA).
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.
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.
=IFERROR(VLOOKUP(999,A2:C7,3,FALSE),"missing")=IFNA(VLOOKUP(999,A2:C7,3,FALSE),"missing")=IFERROR(1/0,"err")=IFNA(1/0,"err")=IFERROR(VALUE("abc"),"err")=IFNA(VALUE("abc"),"err")=IFERROR(INDEX(A2:A7,99),"err")=IFNA(INDEX(A2:A7,99),"err")=IFERROR(VLOOKUP(102,A2:C7,3,FALSE),"missing")=IFNA(VLOOKUP(102,A2:C7,3,FALSE),"missing")=IFERROR(SQRT(-1),"err")=IFNA(SQRT(-1),"err")=IFERROR(IFNA(1/0,"na"),"div")=IFNA(IFERROR(1/0,"div"),"na")Relative engine cost of wrapping the same stream of lookups. The wrapper overhead is tiny and identical; choose by error semantics, not speed.
| Table size | IFERROR | IFNA |
|---|---|---|
| 1,000 rows × 200 lookups | 302.35 ms | 269.76 ms |
| 5,000 rows × 200 lookups | 5039.73 ms | 5125.44 ms |
| 10,000 rows × 200 lookups | 19929 ms | 19847.63 ms |
Choose by whether you want to hide every error or only the 'not found' case.
| Situation | Use | Why |
|---|---|---|
| VLOOKUP/XLOOKUP 'not found' fallback | IFNA | IFNA hides the expected #N/A but still surfaces a real #REF! bug. |
| Any error should show a default | IFERROR | IFERROR collapses every failure mode to one fallback. |
| You must not mask formula bugs | IFNA | IFNA re-throws #DIV/0!, #VALUE!, #REF! so you see them. |
| Workbook must open in Excel 2007/2010 | IFERROR | IFNA doesn't exist before 2013; IFERROR works everywhere. |
| Division that may legitimately be 0/0 | IFERROR | IFERROR catches #DIV/0!; IFNA would let it through. |
| Clean lookup column for a report | IFNA | IFNA gives 'missing' for absent keys without hiding breakage. |
| Nested error handling | Either | Compose them: IFNA inner for #N/A, IFERROR outer for the rest. |
| Successful formula | Either | Both pass the value through unchanged when there's no error. |
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.
| Task | IFERROR / old way | IFNA | What 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. |