The IFERROR function
Returns a value you choose when a formula gives any error, and the formula result otherwise.
How to use IFERROR
IFERROR is a safety net under a formula. It runs the formula you give it, and if the result is an error such as #N/A, #DIV/0! or #VALUE!, it shows your replacement instead. No error means no change: you get the normal result.
Use it where an error is expected and ugly: a lookup for an item that is not on the price list, an average over a month with no sales, or a percentage change from zero. =IFERROR(VLOOKUP(A2,Prices!A:B,2,FALSE),"Not found") shows a readable message instead of #N/A, and =IFERROR(B2/C2,0) keeps a total row from filling up with #DIV/0!.
The second argument can be a number, text in quotes, two double quotes "" for a cell that looks empty, or another formula, such as a second lookup in a different table. Wrap only the part that may fail, not the whole formula: a wide net also hides real mistakes like a misspelled function name or a deleted column. If you only expect "no match" from a lookup, IFNA catches just #N/A and lets other errors through. For division by zero, a test like =IF(C2=0,0,B2/C2) is more honest, because it handles exactly that case.
Syntax
IFERROR(value, value_if_error)- value
- The formula to check.
- value_if_error
- What to show instead of an error, such as 0 or "Not found".
Examples
=IFERROR(110/0,20)| A | B | |
|---|---|---|
| 1 | 0 |
In plain words: 1Divide 10 by 0, and 2if that gives an error, show 0 instead.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Fruit | Price | Not found | |
| 2 | Apple | 4 | ||
| 3 | Pear | 6 | ||
| 4 | Plum | 5 |
Common IFERROR mistakes
IFERROR shows "Not found" for everything, even values that exist
The formula inside is wrong, and IFERROR hides it. A typo in the range or a column number that is too high both end up as the fallback text. Remove IFERROR for a moment to see the real error, fix it, and consider IFNA so that only #N/A is replaced.
My average is too low after adding IFERROR
A replacement of
0counts as a real value in AVERAGE. Return""instead, as in=IFERROR(B2/C2,""), because AVERAGE skips text.I still see #N/A in my total
IFERROR only fixes the cell it sits in. A SUM over a range that contains one error returns that error. Wrap the formulas that produce the errors, not the formula that adds them up.
Practice IFERROR
Read the guide
Questions
Should I use IFERROR or IFNA?
IFNA only catches #N/A, the error a lookup gives when nothing matches. IFERROR also hides real mistakes such as #REF! or #NAME?, so prefer IFNA around lookups.
How do I use IFERROR with VLOOKUP?
Put the VLOOKUP inside IFERROR and give the fallback second, =IFERROR(VLOOKUP(A2,Prices!A:B,2,FALSE),"Not found"). If the lookup finds the value, you get the price. If not, you get the text.
How do I replace #DIV/0! with a blank?
Use two double quotes as the fallback, =IFERROR(B2/C2,""). The cell looks empty, but it holds empty text, so a later +C2 would give #VALUE!. Use SUM or AVERAGE on the column instead.
Which errors does IFERROR catch?
All of them: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? and #NULL!. It cannot tell them apart, which is why a narrower function like IFNA is safer for lookups.
Can I try a second lookup when the first one fails?
Yes, nest IFERROR. =IFERROR(VLOOKUP(A2,Table1,2,FALSE),IFERROR(VLOOKUP(A2,Table2,2,FALSE),"Not found")) tries Table1, then Table2, then shows the text.