The IFERROR function in Excel
IFERROR wraps another formula. If that formula works, you get its result. If it returns any error (#N/A, #DIV/0!, #VALUE!, #REF!, #NAME?…), you get a value you choose instead, such as 0, an empty text or a message.
It's most often used around lookups and divisions, where a missing value or a zero is expected now and then.
Syntax
=IFERROR(value, value_if_error)
| Argument | What it means |
|---|---|
| value | The formula to check. |
| value_if_error | What to return if that formula results in an error. |
Examples
1. Avoid #DIV/0! when dividing
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Region | Revenue | Orders | Per order |
| 2 | North | 5000 | 20 | |
| 3 | South | 0 | 0 | 0 |
South has 0 orders, so B3/C3 would be #DIV/0!. IFERROR shows 0 instead.
2. Normal results pass through unchanged
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Region | Revenue | Orders | Per order |
| 2 | North | 5000 | 20 | 250 |
| 3 | South | 0 | 0 |
5000 / 20 = 250 is not an error, so IFERROR returns it as it is.
3. A friendly message for a failed lookup
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Revenue | Orders | Per order | Revenue of East | |
| 2 | North | 5000 | 20 | Missing | ||
| 3 | South | 0 | 0 |
East isn't in the table, so VLOOKUP returns #N/A and IFERROR shows "Missing".
Common mistakes
Hiding real mistakes. IFERROR also hides typos (#NAME?) and broken references (#REF!), so a wrong formula can look fine.
Fix: Build and test the formula first, then wrap it. For lookups, IFNA only catches "not found" (#N/A) and leaves other errors visible.
Returning "" and then calculating with it. Empty text in a math formula can cause #VALUE!.
Fix: Return 0 when the cell is used in further calculations.
Frequently asked questions
What's the difference between IFERROR and IFNA?
IFERROR catches every error type. IFNA catches only #N/A, the "not found" error from lookups, which is usually safer.
Does XLOOKUP need IFERROR?
Usually not: XLOOKUP has its own if_not_found argument.
Related functions
- IFIF checks a condition and returns one value if it's true and another if it's false.
- VLOOKUPVLOOKUP finds a value in the first column of a table and returns a value from the same row in another column.
- XLOOKUPXLOOKUP searches one column or row and returns the matching value from another, in any direction, with a built-in 'not found' message.