Chapter 09 · intermediate · 5 min
Error values: IFERROR, IFNA, and knowing which to reach for
A formula that fails doesn't crash the sheet — it returns a value that starts with #, and that value then propagates into anything referencing it. Catching these deliberately, rather than letting a stray #DIV/0! sit in a report, is what IFERROR and IFNA are for — but reaching for the wrong one, or reaching for either one too eagerly, trades a visible bug for an invisible one.
The grid
A1/B1 hold numbers for a division that fails on purpose (B1 is 0). F1:G3 hold a small ID lookup table; H1 holds an ID that isn't in it.
The eight # errors
#DIV/0! (division by zero), #N/A (a lookup found no match), #NAME? (Excel doesn't recognise a name or function — often a typo), #NULL! (an invalid range intersection), #NUM! (an invalid number, like a negative under a square root), #REF! (a formula points at a cell that no longer exists, usually after a delete), #VALUE! (the wrong type of argument, like text where a number is expected), and #SPILL! (a dynamic array's landing area is blocked by something else). Each means something different — which is exactly why blanket-catching all of them isn't always the right move.
IFERROR: catch any error, substitute a fallback
IFERROR(formula, fallback) runs formula; if it evaluates to any of the eight errors above, it returns fallback instead:
=IFERROR(A1/B1,"N/A")B1 is 0, so A1/B1 alone would be #DIV/0!. Wrapped in IFERROR, the sheet shows "N/A" instead.
A1 is 100, B1 is 0 — dividing them errors. What does IFERROR substitute instead?
Scroll to see all 9 columns →
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | 100 | 0 | =IFERROR(A1/B1,"N/A") | X1 | Widget | X9 | |||
| 2 | X2 | Gadget | |||||||
| 3 | X3 | Gizmo |
IFNA: catch only #N/A
IFNA(formula, fallback) behaves like IFERROR but only intercepts #N/A specifically — every other error still surfaces. It's the right choice for lookups, where "not found" is an expected, normal outcome and shouldn't be lumped in with a genuine formula bug:
=IFNA(VLOOKUP(H1,F1:G3,2,FALSE),"Not found")H1 is "X9", which isn't in the table, so the raw VLOOKUP would be #N/A.
Lookup with a friendly not-found message
In I1, look up H1 in the ID table (F1:G3) and show "Not found" instead of a raw #N/A when it's missing.
Scroll to see all 9 columns →
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | 100 | 0 | X1 | Widget | X9 | ||||
| 2 | X2 | Gadget | |||||||
| 3 | X3 | Gizmo |
The trap: IFERROR hides bugs
Wrapping an entire complex formula in IFERROR(...,"") makes every failure — a genuine typo in a range reference, a #REF! from a deleted column, a #VALUE! from a formula fed the wrong type — disappear behind the same blank cell as an expected, harmless case. The sheet looks clean and is actually broken. Prefer IFNA for lookups specifically, since "not found" is the only outcome you're actually expecting to suppress; reserve IFERROR for cases where you deliberately want every failure mode treated the same way, and even then wrap the smallest expression you can, not the whole formula.