Excel IFERROR Function
IFERROR Function
The IFERROR function is a premade function in Excel, which returns a value of your choice if a formula gives an error.
If the formula does not give an error, IFERROR returns the normal result of the formula.
It is typed =IFERROR and has the following parts:
=IFERROR(value, value_if_error)
Note: The different parts of the function are separated by a symbol, like comma , or semicolon ;
The symbol depends on your Language Settings.
Value: The formula to check for an error, for example a VLOOKUP or a division.
Value_if_error: What to return if the formula gives an error. It can be text in double quotes, a number, or an empty text "", which makes the cell look empty.
IFERROR catches all of these errors:
| Error | Common cause |
|---|---|
#N/A | A lookup function did not find the value |
#DIV/0! | A number is divided by zero or by an empty cell |
#VALUE! | The formula uses the wrong type of value, like text in a calculation |
#REF! | The formula refers to a cell or column that does not exist |
#NAME? | Excel does not recognize a name in the formula, like a misspelled function |
#NUM! | A number is not valid for the calculation |
#NULL! | Two ranges that do not meet are separated by a space |
How to use the IFERROR function:
- Select a cell (
H4) - Type
=IFERROR - Double click the IFERROR command
- Type the formula that can give an error (
VLOOKUP(H3, B2:E21, 4, FALSE)) - Type (
,) - Type the value to show if there is an error (
"Not found") - Hit enter
Let's have a look at an example!
This is the same Pokemon table as on the XLOOKUP page. Copy it and paste it into cell A1 to follow along:
Example data
A B C D E 1 ID# Name Type 1 Type 2 Total 2 1 Bulbasaur Grass Poison 318 3 2 Ivysaur Grass Poison 405 4 3 Venusaur Grass Poison 525 5 4 Charmander Fire 309 6 5 Charmeleon Fire 405 7 6 Charizard Fire Flying 534 8 7 Squirtle Water 314 9 8 Wartortle Water 405 10 9 Blastoise Water 530 11 10 Caterpie Bug 195 12 11 Metapod Bug 205 13 12 Butterfree Bug Flying 395 14 13 Weedle Bug Poison 195 15 14 Kakuna Bug Poison 205 16 15 Beedrill Bug Poison 395 17 16 Pidgey Normal Flying 251 18 17 Pidgeotto Normal Flying 349 19 18 Pidgeot Normal Flying 479 20 19 Rattata Normal 253 21 20 Raticate Normal 413
Type the labels Search Name in G3 and Total in G4.
Use VLOOKUP to find the Total of a Pokemon based on its Name, and IFERROR to show Not found if the name is not in the table. Type Pikachu in H3:
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | ID# | Name | Type 1 | Type 2 | Total | |||
| 2 | 1 | Bulbasaur | Grass | Poison | 318 | |||
| 3 | 2 | Ivysaur | Grass | Poison | 405 | Search Name | Pikachu | |
| 4 | 3 | Venusaur | Grass | Poison | 525 | Total | Not found | |
| 5 | 4 | Charmander | Fire | 309 | ||||
| 6 | 5 | Charmeleon | Fire | 405 | ||||
| 7 | 6 | Charizard | Fire | Flying | 534 | |||
| 8 | 7 | Squirtle | Water | 314 | ||||
| 9 | 8 | Wartortle | Water | 405 | ||||
| 10 | 9 | Blastoise | Water | 530 | ||||
| 11 | 10 | Caterpie | Bug | 195 | ||||
| 12 | 11 | Metapod | Bug | 205 | ||||
| 13 | 12 | Butterfree | Bug | Flying | 395 | |||
| 14 | 13 | Weedle | Bug | Poison | 195 | |||
| 15 | 14 | Kakuna | Bug | Poison | 205 | |||
| 16 | 15 | Beedrill | Bug | Poison | 395 | |||
| 17 | 16 | Pidgey | Normal | Flying | 251 | |||
| 18 | 17 | Pidgeotto | Normal | Flying | 349 | |||
| 19 | 18 | Pidgeot | Normal | Flying | 479 | |||
| 20 | 19 | Rattata | Normal | 253 | ||||
| 21 | 20 | Raticate | Normal | 413 |
=IFERROR(VLOOKUP(H3, B2:E21, 4, FALSE), "Not found")
VLOOKUP searches the names in B2:B21 and returns the value from column 4 of B2:E21, which is Total. FALSE means exact match.
Pikachu is not in the table, so VLOOKUP gives an error. IFERROR catches the error and returns Not found instead.
Without IFERROR, the result is #N/A:
| G | H | |
|---|---|---|
| 3 | Search Name | Pikachu |
| 4 | Total | #N/A |
=VLOOKUP(H3, B2:E21, 4, FALSE)
Now type Squirtle in H3. Squirtle is in the table, so there is no error, and IFERROR returns the result of VLOOKUP:
| G | H | |
|---|---|---|
| 3 | Search Name | Squirtle |
| 4 | Total | 314 |
Note: Text in a formula must be inside double quotes: " "
IFERROR with Division
Dividing by zero gives the #DIV/0! error. This often happens when a cell is 0 or empty.
This table shows how many battles some trainers have had, and how many they won. Copy it and paste it into cell A1 of a new sheet:
Example data
A B C 1 Trainer Battles Wins 2 Ash 20 13 3 Misty 8 6 4 Brock 10 4 5 Dawn 16 10 6 Gary 0 0
Type Win Rate in D1. The win rate is the wins divided by the battles. Type this formula in D2 and fill it down to D6:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Trainer | Battles | Wins | Win Rate |
| 2 | Ash | 20 | 13 | 0.65 |
| 3 | Misty | 8 | 6 | 0.75 |
| 4 | Brock | 10 | 4 | 0.4 |
| 5 | Dawn | 16 | 10 | 0.625 |
| 6 | Gary | 0 | 0 | #DIV/0! |
=C2/B2
Gary has not had any battles yet. His formula in D6 is =C6/B6, which divides 0 by 0, so the result is #DIV/0!.
Wrap the division in IFERROR. Change the formula in D2, and fill it down to D6 again:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Trainer | Battles | Wins | Win Rate |
| 2 | Ash | 20 | 13 | 0.65 |
| 3 | Misty | 8 | 6 | 0.75 |
| 4 | Brock | 10 | 4 | 0.4 |
| 5 | Dawn | 16 | 10 | 0.625 |
| 6 | Gary | 0 | 0 | No battles |
=IFERROR(C2/B2, "No battles")
The other win rates stay the same. Only the error is replaced.
Tip: Format column D as Percentage to show 0.65 as 65%. See Number Formats.
IFERROR Can Hide Mistakes
IFERROR hides every error, including errors that come from a mistake in your formula. Then the mistake is hard to notice.
Go back to the Pokemon table. This formula has a mistake: the column number is 5, but B2:E21 has only 4 columns. Type Squirtle in H3:
| G | H | |
|---|---|---|
| 3 | Search Name | Squirtle |
| 4 | Total | Not found |
=IFERROR(VLOOKUP(H3, B2:E21, 5, FALSE), "Not found")
Squirtle is in the table, but the result is Not found. The real error is #REF!, because there is no column 5 in the range. IFERROR hides it.
Note: Make sure your formula works before you wrap it in IFERROR. Use IFERROR only for errors you expect, like a name that is not in the table.
IFNA: Catch Only #N/A
The IFNA function works like IFERROR, but it only catches the #N/A error. All other errors are still shown.
=IFNA(value, value_if_na)
Lookup functions return #N/A when they do not find the value. That makes IFNA a good choice for lookups: a missing name shows your message, but a mistake in the formula still shows an error.
Use IFNA with the same mistake as above. Squirtle is still in H3:
| G | H | |
|---|---|---|
| 3 | Search Name | Squirtle |
| 4 | Total | #REF! |
=IFNA(VLOOKUP(H3, B2:E21, 5, FALSE), "Not found")
Now you can see the #REF! error, and fix the column number to 4. After the fix, a name that is not in the table, like Pikachu, still returns Not found.
Note: IFNA is available in Excel 2013 and later.
See Also
XLOOKUP has its own if_not_found part, so you do not need IFERROR or IFNA around it.
IFERROR is often used together with VLOOKUP, HLOOKUP and INDEX and MATCH. To check conditions other than errors, use IF.
