The Excel IFERROR function, explained interactively.
IFERROR wraps another formula and substitutes a fallback value if that formula produces any error —#N/A, #VALUE!, #DIV/0!, or any other. The most common use is cleaning up lookup failures so dashboards stay readable.
How to use IFERROR
- Write the formula you want to protect — typically a lookup like
VLOOKUP(A2, catalog, 2, FALSE). - Wrap it:
=IFERROR(…,— so IFERROR is the outer function and your original formula is its first argument. - Supply the value_if_error — what IFERROR returns when the inner formula errors. Text, number, empty string (
""), or another formula. - Close both parentheses. When the inner formula succeeds, IFERROR passes its result through unchanged; only on error does the fallback appear.
Try the demo below — change the SKU to one the catalog doesn’t contain and watch #N/A disappear under the fallback.
#N/A, #VALUE!, #DIV/0!, any of them.value errors. Text, number, empty string, or another formula. Plain "" blanks out the cell.| A | B | |
|---|---|---|
| 1 | SKU | Name |
| 2 | A-101 | Pencil |
| 3 | A-102 | Notebook |
| 4 | B-210 | Stapler |
| 5 | C-305 | Whiteboard |
| 6 | C-306 | Marker set |
| 7 | ||
| 8 | IFERROR result | Pencil |
A-101 — IFERROR passes its result through unchanged. The fallback is only used on error.IFERROR syntax and arguments
Two positional arguments — the formula to try, and the fallback. See Microsoft’s official IFERROR reference for the canonical specification.
VLOOKUP, INDEX/MATCH) or an arithmetic expression that could divide by zero. IFERROR evaluates value normally and returns its result when no error occurs — it does not short-circuit or change behaviour on success.value produces any error. Accepts text ("Not found"), numbers (0), empty strings (""), or another formula. The fallback is always evaluated when an error occurs, so avoid expensive formulas here if the inner expression errors frequently.IFERROR examples
Four patterns that cover 95% of real-world IFERROR use.
Example 1: IFERROR wrapping a VLOOKUP
The canonical case — a lookup that might miss shouldn’t show #N/A in a report.
When A2 exists in the catalog, IFERROR returns the VLOOKUP result. When it doesn’t, the fallbackNot found appears instead. Consider using XLOOKUP’s built-in if_not_found argument when available — it’s strictly safer because it only catches missing-match errors.
Example 2: IFERROR to prevent #DIV/0!
Division problems are unavoidable when a denominator legitimately can be zero.
When C2 is zero, Excel normally returns #DIV/0!. IFERROR substitutes 0 so downstream totals still add up. Use this carefully — the substituted zero is a real zero that affects averages and other aggregates.
Example 3: IFERROR with an empty-string fallback
For dashboards, a blank cell often reads better than a literal “Not found”.
"" renders as an empty cell. Note it is technically an empty string, not a truly blank cell — downstream ISBLANK will still return FALSE. For a truly blank result, use IF with an explicit existence test instead.
Example 4: IFERROR nested to chain two lookups
Try the primary catalog, fall back to an archive catalog, finally show a human-readable error.
Nesting IFERRORs builds a cascade: try each source in turn, show a readable message only when every attempt fails. Useful for SKU lookups that span multiple price lists or legacy systems. More than three nested levels usually signals a data structure problem — consider a helper column.
Common IFERROR errors and fixes
IFERROR itself rarely errors — but using it carelessly introduces subtler bugs. Four to watch for.
IFERROR hiding a genuine bug
Cause: a typo in the inner formula (misspelled function name, deleted range, mismatched types) produces #NAME? or #REF!. IFERROR catches them both and silently shows the fallback, masking the underlying bug.
Switch to IFNA when you only want to catch missing-match errors. IFNA still reveals real formula bugs; only #N/A gets the fallback.
IFERROR returning 0 distorts totals
Cause: wrapping =IFERROR(something, 0) to hide #DIV/0! has a hidden side effect — those zeros are real numbers that drag down averages.
Use "" as the fallback instead, or filter out the blank cells in downstream aggregates withAVERAGEIFS. Check whether a zero is the right answer before using it.
IFERROR breaking XLOOKUP’s if_not_found
Cause: wrapping XLOOKUP in IFERROR overrides XLOOKUP’s own built-in if_not_found argument — both would fire on a miss, and the outer IFERROR wins.
Pick one. If you’re on XLOOKUP, supply its if_not_found argument directly and skip IFERROR. IFERROR is meant for older functions that lack a built-in fallback.
IFERROR in Excel 2003 or earlier
Cause: IFERROR was introduced in Excel 2007. Opening a sheet in Excel 2003 or older shows #NAME? for every IFERROR cell.
Use the older IF(ISERROR(x), fallback, x) pattern when compatibility matters. It evaluates x twice — slower, but works everywhere.
IFERROR vs IFNA, ISERROR & ISERR
Four ways to test for an error condition. Pick by how broad the catch should be and whether you want a replacement or just a boolean.
| Function | Catches | Returns | Use when |
|---|---|---|---|
| IFERROR | Every error type | Fallback value | Quick visual cleanup; you’ve already validated the formula. |
| IFNA | #N/A only | Fallback value | Lookups — you want missing-match handling without hiding real bugs. |
| ISERROR | Every error type | TRUE / FALSE | Conditional formatting, or branching inside a larger IF formula. |
| ISERR | All errors except #N/A | TRUE / FALSE | Rare — you want to flag broken formulas without flagging legitimate misses. |
Rule of thumb: reach for IFNA first when wrapping a lookup, because it keeps real formula errors visible. Reach for IFERROR when the inner formula is arithmetic and you genuinely want to treat any failure the same way. Almost never use ISERROR/ISERR for substitution — they’re for conditional logic, where IFERROR’s two-argument form is overkill.
IFERROR frequently asked questions
6.01What’s the difference between IFERROR and IFNA?▸
IFERROR catches every error (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, #NULL!). IFNA catches only #N/A. PreferIFNA around lookups so real formula bugs surface — IFERROR can silently hide typos.
6.02Can IFERROR hide a real bug in my formula?▸
Yes — that’s its biggest risk. IFERROR treats #NAME? (misspelled function) and #REF! (deleted range) exactly like #N/A. Use IFNA when you only want to catch missing-lookup cases, and reserve IFERROR for inputs you’ve already validated.
6.03Should I use IFERROR with VLOOKUP or XLOOKUP?▸
With VLOOKUP and INDEX/MATCH, yes — wrap in IFERROR or IFNA for a clean fallback. With XLOOKUP, use its built-in if_not_found argument instead; it’s simpler and more specific.
6.04Can IFERROR catch errors inside an array formula?▸
Yes, but per element. =IFERROR(1/A2:A10, 0) returns 0 for each zero divisor. In legacy Excel you may need Ctrl+Shift+Enter; in Excel 365 and Google Sheets it works naturally with dynamic arrays.
6.05How do I return a blank cell from IFERROR?▸
Pass an empty string as value_if_error: =IFERROR(VLOOKUP(...), ""). The cell renders empty. Note that other formulas treat "" as a zero-length string, not a truly empty cell — use ISBLANK with caution downstream.
Microsoft Excel is a registered trademark of Microsoft Corporation. Google Sheets is a trademark of Google LLC. Formula Gym is not affiliated with, endorsed by, or sponsored by either company.