IFERROR vs IFNA: don't hide the errors you need
Both replace an error with a fallback value — the difference is scope. IFERROR swallows EVERY error; IFNA only catches #N/A, the "not found" signal. Around lookups that distinction decides whether a typo in your range shows up or gets silently papered over.
The differences at a glance
| IFERROR | IFNA | |
|---|---|---|
| #N/A (lookup not found) | Caught | Caught |
| #DIV/0!, #VALUE!, #REF!, #NAME? | Caught — including genuine bugs | NOT caught — real errors stay visible |
| Typical use | Divisions that may hit zero, conversions | Wrapping VLOOKUP/XLOOKUP/MATCH |
| Risk | A broken range or typo shows your fallback text instead of failing loudly | Minimal — only the expected miss is handled |
| Compatibility | Universal (Excel 2007+, all Sheets & LO versions we test) | Excel 2013+, Sheets, LibreOffice (verified) |
Which should you use?
- IFERROR — The formula can only fail one obvious way and any error means the same thing — e.g. a rate calculation that divides by a count that may be zero.
- IFNA — Every lookup. "Not found" gets your friendly fallback, while a deleted column (#REF!) or misspelled function (#NAME?) still surfaces as an error you'll notice and fix.
Compatibility (from executed tests)
Both execute correctly in every LibreOffice release we test, Google Sheets, and modern Excel (IFNA arrived in Excel 2013). XLOOKUP users get a third option: its built-in 4th argument handles only the not-found case, like IFNA, without wrapping anything.
Example formulas
| IFNA around a lookup (recommended) | =IFNA(VLOOKUP(D2,A2:B10,2,FALSE),"Not found") |
| IFERROR for an expected division error | =IFERROR(A2/B2,0) |
Full per-version details on each function page: IFERROR · IFNA.