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

B1=IFERROR(10/0,0)
AB
10

In plain words: Divide 10 by 0, and if that gives an error, show 0 instead.

D1=IFERROR(VLOOKUP("Kiwi",A2:B4,2,FALSE),"Not found")
ABCD
1FruitPriceNot found
2Apple4
3Pear6
4Plum5

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 0 counts 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

Related functions