Excel functions / Lookup & reference
IFERROR
Shows a value of your choice instead of an error such as #N/A or #DIV/0!.
Syntax
=IFERROR(value, value_if_error)Arguments
value | The formula to try. |
|---|---|
value_if_error | What to show if that formula returns any error. |
How it works
IFERROR runs a formula and, if it fails, shows something friendlier instead. It is most often wrapped around lookups, so a missing item shows "Not found" or 0 instead of #N/A.
Use it with care: it hides every error, including real mistakes such as a misspelt function.
Example
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Rep | Sales | ||
| 2 | East | Ana | 500 | None | |
| 3 | West | Ben | 300 | ||
| 4 | East | Cal | 250 | ||
| 5 | West | Dee | 700 | ||
| 6 |
Result: None. Zed is not in the list, so the lookup fails and IFERROR shows None.
Tips
- Get the formula working first, then add IFERROR, so it does not hide a mistake while you build.
Practise IFERROR
Try it yourself in a real spreadsheet, with instant feedback on every answer.