IFERROR function in Excel
Last updated
IFERROR returns the result of a formula, or a value of your choice if that formula gives an error. For example, =IFERROR(VLOOKUP(E2,$A$2:$C$6,3,FALSE),"Not found") shows the price, or Not found instead of #N/A when the code isn’t in the list. =IFERROR(C2/B2,0) shows 0 instead of #DIV/0!.
IFERROR syntax
=IFERROR(value, value_if_error)
- value
- The formula to try, such as a division
C2/B2or a lookupVLOOKUP(…). - value_if_error
- What to show when it gives any error (#N/A, #DIV/0!, #VALUE!…):
0, a text in double quotes,""for an empty-looking cell, or another formula.
Practice IFERROR here
Type your formula straight into the sheet. It’s checked like real Excel, and you can write it with commas or semicolons.
Hide #DIV/0! in a rate
The conversion rate is orders divided by visits. Some campaigns had no visits yet, so =C2/B2 shows #DIV/0!. Fill D2:D6 with the rate, and 0 when it can’t be calculated.
| Campaign | Visits | Orders | Conversion rate |
| Spring newsletter | 1200 | 36 | |
| Trade fair QR code | 0 | 0 | |
| Search ads | 850 | 17 | |
| Partner blog | 2400 | 60 | |
| Radio spot | 0 | 0 |
How to add IFERROR to a formula, step by step
- Write the formula on its own first and check that it works on the rows without errors.
- Click just after the equals sign and type
IFERROR(. - Go to the end of the formula and type a comma, then the value to show instead, such as
0or"Not found". - Close the bracket, press Enter and copy the formula down.
IFERROR examples
Rate without #DIV/0!
=IFERROR(C2/B2,0)
Shows 0 when B2 is empty or 0, instead of #DIV/0!.
Lookup with a message
=IFERROR(VLOOKUP(E2,$A$2:$C$6,3,FALSE),"Not found")
Returns the price, or Not found when the code isn’t in the list.
Missing price counted as 0
=IFERROR(VLOOKUP(A2,$E$2:$F$5,2,FALSE),0)*B2
A missing price becomes 0, so the column stays numeric and SUM still works.
Second list, then a message
=IFERROR(VLOOKUP(A2,$D$2:$E$4,2,FALSE),IFERROR(VLOOKUP(A2,$G$2:$H$4,2,FALSE),"Not found"))
Tries the first list, then the second, and only shows the message if neither has the code.
Common IFERROR mistakes and how to fix them
| Result | Cause | Fix |
|---|---|---|
| Not found for every row | The lookup itself is broken (wrong range, wrong column number, range not locked with $), and IFERROR hides the real error. | Remove IFERROR, fix the lookup until the existing codes work, then wrap it again. |
| A total that looks too low | IFERROR returns 0 for real mistakes too, such as a typo in a code, so those rows quietly add nothing. | Use a visible text such as "Check" while you build the sheet, and switch to 0 once the data is clean. |
| #VALUE! after IFERROR | The text returned by IFERROR is used in a calculation further on, e.g. "New" * 2. | Return a number (0) when the result feeds other formulas, or test the cell with ISNUMBER. |
| Wrong value, no error | Brackets missing in the first argument, e.g. IFERROR(C2-B2/B2,…) divides before subtracting. | Write (C2-B2)/B2 inside IFERROR. |
IFERROR vs IFNA vs IF
IFERROR catches every error. IFNA (Excel 2013 and later) only catches #N/A, so a lookup that can’t find the value shows your message, while a broken formula still shows its real error. For lookups, IFNA is safer; for divisions, use IFERROR or test the divisor first: =IF(B2=0,0,C2/B2). XLOOKUP also has its own if_not_found argument, which makes IFERROR unnecessary around it.
Frequently asked questions
How do I hide #DIV/0! in Excel?
Wrap the division in IFERROR: =IFERROR(C2/B2,0), or use "" to leave the cell looking empty.
How do I replace #N/A from VLOOKUP?
Put the VLOOKUP inside IFERROR: =IFERROR(VLOOKUP(E2,$A$2:$C$6,3,FALSE),"Not found").
What is the difference between IFERROR and IFNA?
IFERROR replaces any error, IFNA only #N/A. IFNA lets other errors, like a wrong column number, stay visible so you can fix them.
Can I nest IFERROR?
Yes. The value_if_error can be another formula, including another IFERROR, which is how you search a second list when the first one fails.
Keep practising
All 5 IFERROR exercises, with explanations and progress tracking.
Open the IFERROR practice set