The Excel IFERROR function is the universal safety net: if a formula works, you get its result; if it raises any error, you get your fallback instead. One wrapper turns a report full of #DIV/0! and #N/A into clean, readable output. Its breadth is also its danger — IFERROR catches typos and broken references just as happily as expected misses, which is why lookups are often better served by the narrower IFNA.
Syntax
| Argument | Description | |
|---|---|---|
value | Required | The formula or expression to evaluate — usually a calculation or lookup that might fail. |
value_if_error | Required | What to return when value produces any error: #N/A, #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM!, or #NULL!. |
Available in: Excel 2007 and every version since, desktop and web. (In truly ancient workbooks you may meet the pre-2007 idiom IF(ISERROR(…), …) — IFERROR replaced it and evaluates the formula only once.)
Clean up divisions and lookups
Average order value is revenue ÷ orders — which detonates whenever orders is zero. IFERROR keeps the report presentable:
| A | B | C | |
|---|---|---|---|
| 1 | Revenue | Orders | Avg order |
| 2 | 8,400 | 120 | 70 |
| 3 | 5,100 | 0 | No orders |
| 4 | 9,660 | 140 | 69 |
The other classic customer is a lookup that might miss:
The fallback doesn’t have to be text. A zero keeps downstream sums working, and a second formula makes a two-stage lookup:
Try it: interactive IFERROR demo
Pick what the inner formula produces and see what IFERROR hands back. Spoiler: it catches everything — including the typo.
IFERROR vs IFNA: choose the narrowest net
For lookup formulas, only one error is expected: #N/A, the “value not found” signal. Every other error — #REF! from a deleted column, #NAME? from a typo, #VALUE! from bad arguments — means the formula itself is broken, and you want to see that. IFNA catches only #N/A and lets real bugs through:
Reserve IFERROR for cases where any failure genuinely means the same thing — like the divide-by-zero example, where #DIV/0! is the only error the formula can realistically produce.
Workflow tip: build the formula bare first and confirm it works. Add the IFERROR wrapper last, once the only errors left are the expected ones. Debugging through a safety net is miserable.
Pitfalls & gotchas
Pitfall: IFERROR hides real bugs. This is the big one. Misspell VLOOKUP, delete a referenced column, point at the wrong sheet — IFERROR serenely reports “Not found” and the mistake ships. If only a lookup miss should be caught, use IFNA.
Pitfall: "" fallbacks that poison math. =IFERROR(A2/B2, "") looks clean, but the empty string is text — a later =C2*1.1 on that cell returns #VALUE!. If downstream formulas do arithmetic, fall back to 0 instead, or handle blanks explicitly.
Pitfall: blanket-wrapping every formula. Wrapping a whole sheet in IFERROR “to be safe” turns every future mistake into silence. Wrap only formulas with a known, expected failure mode — and make the fallback descriptive (“No orders”), not invisible.
IFERROR itself rarely errors — but it does not catch a fallback that fails. In =IFERROR(X, Y), if X errors and Y errors too, you see Y’s error. And it can’t catch what isn’t an error: a lookup returning a wrong-but-valid value sails straight through.
Practice workbook
Frequently asked questions
What errors does IFERROR catch?
Should I use IFERROR or IFNA with VLOOKUP and XLOOKUP?
How do I make IFERROR return a blank cell?
=IFERROR(A2/B2, ""). Be aware the result is text, not a true blank — arithmetic on it later gives #VALUE!. If the cell feeds calculations, return 0 and hide it with the custom number format 0;-0;;@ if needed.Why does my IFERROR still show an error?
Can I nest two lookups with IFERROR?
=IFERROR(VLOOKUP(E2, TableA, 2, 0), VLOOKUP(E2, TableB, 2, 0)). If the first lookup misses, Excel tries the second. Chain another IFERROR for a third source.Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class