IFERROR in Excel: how to use it
IFERROR shows a value of your own when a formula gives an error, and otherwise just the result.
Try the first exercises for freeIn the lesson Catching errors with IFERROR
What does IFERROR do?
IFERROR checks whether a formula gives an error, such as #N/A or #DIV/0!. If there's no error, you simply see the result. If there is one, you see what you gave it instead: a text, a 0 or an empty cell.
You wrap IFERROR around a formula that works, but that sometimes rightly finds nothing. Don't use it to hide a broken formula: you'd also hide the errors you actually want to see.
When do you use IFERROR?
- A neat message if a code isn't in the price list: =IFERROR(VLOOKUP(E2,$A$2:$C$50,3,FALSE),"Unknown").
- A 0 instead of #DIV/0! when the cell you divide by is empty: =IFERROR(A2/B2,0).
- An empty cell if a character isn't found: =IFERROR(SEARCH("@",A2),"").
- Catching only #N/A and leaving other errors visible: =IFNA(XLOOKUP(E2,A2:A50,C2:C50),"Not found").
Syntax
The syntax is the fixed pattern of a function. The words in the brackets are placeholders: that's where you fill something in yourself, such as a cell, a number or a piece of text. Everything else you type exactly as it is.
The result, or your own value for any error.
Filled in: =IFERROR(A2/B2,0)
This is how to read the formula, part by part:
| Part | What it means | In the example |
|---|---|---|
= | Every formula starts with an equals sign. Without =, Excel treats what you type as ordinary text. | = |
IFERROR | The name of the function: that's how Excel knows what to do. You can type it in lower case, Excel puts it in capitals itself. | IFERROR |
( ) | Straight after the name come the brackets. Inside them is what the function needs to work: the arguments. | ( ) |
value | The formula that might give an error. | A2/B2 |
value_if_error | What you want to see if there's an error, such as "Not found" or 0. | 0 |
, | A comma separates the arguments from each other. | , |
IFERROR step by step
E1 contains =IFERROR(VLOOKUP("P-999",A2:C5,3,FALSE),"Not found"). The code doesn't exist, so you see the message instead of #N/A. Change the numbers or the formula and see what happens.
This is how to build the formula yourself:
- First write the formula you want to protect, and check that it works. Here VLOOKUP looks up the code P-999.
=VLOOKUP("P-999",A2:C5,3,FALSE) - Click just after the equals sign and type IFERROR with a bracket. Your formula is now inside IFERROR.
=IFERROR(VLOOKUP("P-999",A2:C5,3,FALSE) - After your formula, type a comma and what you want to see if there's an error: "Not found".
=IFERROR(VLOOKUP("P-999",A2:C5,3,FALSE),"Not found" - Close the bracket and press Enter: the code doesn't exist, so you see the message instead of #N/A.
=IFERROR(VLOOKUP("P-999",A2:C5,3,FALSE),"Not found")
More examples
Dividing by an empty cell
C2 contains =IFERROR(A2/B2,0). Without IFERROR, this gave #DIV/0!, because B2 is empty. Type 60 in B2, and you get 20.
Just the result when all goes well
E1 contains =IFERROR(VLOOKUP("P-204",A2:C5,3,FALSE),"Not found"). The code exists, so IFERROR simply lets the result through: 219.
Catching only #N/A with IFNA
E1 contains =IFNA(VLOOKUP("P-999",A2:C5,3,FALSE),"Not found"). IFNA only catches #N/A. Another error, such as a column number that's too large, stays visible.
Use it deliberately
IFERROR hides every error, including one caused by a typo in your formula. Only use it once your formula works. If you only want to catch #N/A, choose IFNA.
Where it often goes wrong
- An empty result "" can cause a #VALUE! further on if you calculate with it.
Practise this in the lesson Catching errors with IFERROR. The first exercises are free.