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

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

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.

Related functions