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 (Excel for the web, Sheets & LibreOffice executed; desktop Excel per docs)
Both execute correctly in every LibreOffice release we test and in our executed Google Sheets run; modern Excel per Microsoft's docs (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.