IFERROR in Google Sheets
Returns a value you choose when a formula gives any error, and the formula result otherwise.
In Google Sheets
IFERROR works the same way in Google Sheets. There you may also leave out the second argument, which gives an empty cell.
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.