The IFERROR function in Excel
IFERROR swaps an error for something readable.
What IFERROR does
It evaluates a formula. If the formula works, you get its result. If it returns an error such as #N/A or #DIV/0!, you get the replacement you chose instead. It is most often wrapped around a lookup so a missing match shows Not found.
Syntax
=IFERROR(value, value_if_error)
- value
- The formula to try, for example a lookup.
- value_if_error
- What to show if it returns an error, such as "Not found" or 0.
Worked example
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | ID | Product | Price | Look up ID | Price | |
| 2 | 1001 | Notebook | 8 | 1999 | Not found | |
| 3 | 1004 | Desk lamp | 25 | |||
| 4 | 1007 | Backpack | 40 | |||
| 5 | 1010 | Headphones | 60 |
ID 1999 is not in the table, so VLOOKUP would return #N/A. IFERROR shows Not found instead.
Now you try
A fresh exercise every time. Press Another exercise as often as you like.
Where you will use it
Data cleanup
Tidy up reports that show error codes.
Finance
Show 0 instead of a divide-by-zero error.
Sales & Ops
Show a clear message when a lookup finds nothing.
Common mistakes
- Hiding real problems. Wrap only the part where an error is expected, so genuine mistakes still show up.
- Wrapping an entire long formula, which masks typos and wrong references as well.
- Returning an empty string and then doing maths on it. Text in a calculation causes a #VALUE! error.
The IFERROR course
- 1Handle a missing matchFree practice belowPractise
- 2Blank instead of an errorComing soon
- 3Fallback lookupsComing soon
- 4Spot errors you should not hideComing soon
- 5Clean up a messy reportComing soon
Common questions
What is the difference between IFERROR and IFNA?
IFNA only catches #N/A errors, so it hides fewer genuine problems. IFERROR catches every kind of error.
What can I show instead of an error?
Text such as Not found, an empty string, or 0. Pick whatever suits the report.