Function guides ยท Logical
IFERROR function
Returns a value you choose when a formula results in an error.
Syntax
=IFERROR(value, value_if_error)
| Argument | What it is |
|---|---|
value | The formula to evaluate. |
value_if_error | What to return if that formula is any error. |
What IFERROR does
IFERROR runs a formula and, if the formula produces any error, returns a replacement you choose. If there is no error, you get the normal result.
It is the cleanup function. Reports full of #N/A and #DIV/0! look broken even when the logic is sound, and IFERROR lets you decide what a reader sees instead. The cost is that it also hides errors you wanted to know about.
Excel has a family of tools for the same job. IFNA catches only #N/A, which is the right choice around lookups. ISERROR and ISNA return TRUE or FALSE and are usually combined with IF in older workbooks, where you will still see =IF(ISERROR(A1),0,A1). IFERROR does the same in one step, and it has been available since Excel 2007.
One habit worth building: when you inherit a workbook full of IFERROR wrappers returning 0, temporarily remove one and see what the error was. A surprising number of them turn out to hide a broken reference.
IFERROR examples
1. Clean up a divide by zero
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Profit | Revenue | |
| 2 | Kits | 340 | 0 | - |
A margin calculation fails when revenue is zero. IFERROR catches the #DIV/0! and returns a dash instead, so a report does not fill up with errors.
2. Wrap a lookup that may miss
| A | B | |
|---|---|---|
| 1 | SKU | Price |
| 2 | A-10 | 12.5 |
| 3 | B-22 | 34 |
| 4 | Look up: | Z-99 |
| 5 | ||
| 6 | Not found |
The SKU Z-99 is not in the table, so VLOOKUP would show #N/A. IFERROR swaps the error for the words "Not found". The same formula on an SKU that exists returns the price.
3. A good value passes straight through
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Profit | Units | |
| 2 | Kits | 340 | 4 | 85 |
When the formula works, IFERROR returns its result and the second argument is never used. Here 340 divided by 4 is 85.
Common IFERROR mistakes
- Hiding real problems. IFERROR catches every error, including #REF! from a deleted column and #NAME? from a typo. A formula that quietly returns 0 for those can be wrong for months. Use IFNA when only #N/A is expected.
- Returning 0 for missing data. A zero in a column that gets summed or averaged changes the result. Return an empty string or a dash if the cell is only a label.
- Wrapping everything while building. Add IFERROR last. While the formula is being written, you want to see the errors.
- Putting IFERROR around too much.
=IFERROR(SUM(...)+VLOOKUP(...),0)hides which part failed. Wrap the lookup only.
IFERROR on Excel job tests
Two situations produce IFERROR on a test. One is a plain instruction: "if the lookup fails, show Unknown". The other is hidden in the data, a row with a missing ID or a zero denominator, and the test wants to see whether you clean it up.
Wrap only the formula that can fail, and return exactly what the instruction says. "Show a blank" means "". "Show zero" means 0. Text should match the wording, because "Not Found" and "Not found" are different strings.
Do not use it to hide an error in your own formula. If you see #REF! or #NAME?, fix the formula.
Related functions
IFERROR questions
- What errors does IFERROR catch?
- All of them: #N/A, #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM! and #NULL!.
- What is the difference between IFERROR and IFNA?
- IFNA catches only #N/A, which is what a lookup returns when nothing matches. IFERROR also hides every other mistake, so IFNA is safer around lookups.
- Can IFERROR return a blank?
- Yes. Use two double quotes as the second argument:
=IFERROR(A2/B2,""). - Does IFERROR slow down a workbook?
- Not meaningfully. It evaluates the first argument once.
Practice IFERROR
Try it in a working grid: the free lesson IFERROR and IFNA: handling errors on purpose (module 18 of Analyst) has checked IFERROR exercises.
Practice this function