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
Two small tables with headings in row 1. A1:D4 is revenue and orders per outlet, with a per-order column to fill; Cheras had no orders at all. F1:G4 is an ID-to-product lookup table, and H2 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(B3/C3,"N/A")Cheras took no orders, so revenue per order is 0 divided by 0, and B3/C3 alone is #DIV/0! — the first step of the breakdown under the grid shows it. Wrapped in IFERROR, the sheet shows "N/A" instead. The same formula on Bangsar's row would simply return 30; IFERROR only changes anything when there's an error to catch.
Cheras has 0 revenue and 0 orders — dividing them errors. What does IFERROR show instead?
Scroll to see all 9 columns →
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Outlet | Revenue | Orders | Per order | ID | Product | Look up | Product | |
| 2 | Bangsar | 1200 | 40 | X1 | Widget | X9 | |||
| 3 | Cheras | 0 | 0 | =IFERROR(B3/C3,"N/A") | X2 | Gadget | |||
| 4 | Puchong | 900 | 30 | X3 | Gizmo |
How Excel works it out · run it to see each value
- 1B3/C3=?no orders to divide by
- 2IFERROR(B3/C3,"N/A")=?the error, caught
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(H2,F2:G4,2,FALSE),"Not found")H2 is "X9", which isn't in the table, so the raw VLOOKUP would be #N/A.
Lookup with a friendly not-found message
In I2, look up H2 in the ID table (F2:G4) 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 | Outlet | Revenue | Orders | Per order | ID | Product | Look up | Product | |
| 2 | Bangsar | 1200 | 40 | X1 | Widget | X9 | |||
| 3 | Cheras | 0 | 0 | X2 | Gadget | ||||
| 4 | Puchong | 900 | 30 | 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.