How to Use IFERROR in Google Sheets: Comprehensive Guide

IFERROR returns a value you choose when a formula produces an error, and the formula's normal result otherwise. It keeps reports clean by replacing #N/A, #DIV/0! and friends with a blank, a zero or a friendly message.

Syntax

=IFERROR(value, [value_if_error])

value: The formula or value to evaluate.
value_if_error [Optional]: What to return if value is an error. If omitted, IFERROR returns an empty cell.

Basic examples

=IFERROR(A1 / B1, 0)

Returns 0 instead of #DIV/0! when B1 is zero or empty.

=IFERROR(VLOOKUP(E2, A2:C100, 3, FALSE), "Not found")

Shows Not found when the lookup key does not exist, instead of #N/A.

=IFERROR(A1 / B1)

With no second argument, the cell stays empty on error.

Which errors does it catch?

IFERROR catches every error type:

#N/A: a lookup that found nothing
#DIV/0!: division by zero
#VALUE!: the wrong type of argument
#REF!: an invalid reference
#NAME?: an unrecognized function or text
#NUM!: an invalid number
#ERROR!: a formula parse error

The risk: hiding real problems

Because it catches everything, IFERROR can bury genuine mistakes. A typo in a function name, a deleted column or a broken range all become your fallback value, and the sheet looks fine while giving wrong numbers.

Good habits:

Build and test the formula without IFERROR, and add it only at the end.
Use a visible fallback. "Check data" or "n/a" is easier to spot than 0 or a blank.
Wrap only the part that can legitimately fail, not the whole formula:

=A1 * IFERROR(VLOOKUP(E2, Rates!A:B, 2, FALSE), 1)

IFNA: a safer choice for lookups

IFNA catches only #N/A, the error you get when a lookup finds no match. Other errors still show up, so real bugs are not hidden:

=IFNA(VLOOKUP(E2, A2:C100, 3, FALSE), "Not found")

Prefer IFNA whenever the only expected failure is "not found". Use IFERROR when several kinds of error are acceptable, such as division by zero and lookup misses together.

Practical patterns

Fill a column of lookups

=ARRAYFORMULA(IFERROR(VLOOKUP(A2:A, Prices!A:B, 2, FALSE), ""))

Safe averages and percentages

=IFERROR(SUM(B2:B10) / COUNT(B2:B10), 0)

=IFERROR(C2 / B2, "")

Fall back to another source

Chain IFERROR to try a second lookup when the first fails:

=IFERROR(VLOOKUP(A2, Sheet1!A:B, 2, FALSE), IFERROR(VLOOKUP(A2, Sheet2!A:B, 2, FALSE), "Not in either"))

Convert text safely

=IFERROR(VALUE(A2), A2)

Converts text to a number when it can, and keeps the original when it can't.

Count or sum around errors

If a range contains errors, SUM fails too. Clean the range with IFERROR in an ARRAYFORMULA:

=SUM(ARRAYFORMULA(IFERROR(A2:A100, 0)))

Common mistakes

Fallback that looks like real data. Returning 0 for a missing price makes totals look right when they aren't.
Wrapping everything. =IFERROR(huge formula, "") hides every bug in the formula.
Expecting it to fix the cause. IFERROR only changes what is displayed. If you see many fallback values, investigate the data: trailing spaces, text-versus-number mismatches, and missing keys are the usual causes.
Using it for logical checks. If you want to branch on a condition, use IF. IFERROR is for errors only.

IFERROR vs. IF(ISERROR()) vs. IFNA

IFERROR(x, y) is a shorter form of IF(ISERROR(x), y, x) that evaluates x only once. IFNA is the narrower version for #N/A only.

Frequently asked questions

Does IFERROR slow down the sheet? No, not noticeably.

Can I return a formula in the fallback? Yes. The second argument can be any expression.

What does it return if I leave out the second argument? An empty cell.

Does it work inside ARRAYFORMULA? Yes, and it handles each row separately.

Related Functions

IF: Return different values based on a condition.
IFS: Test several conditions in order.
VLOOKUP: A common source of #N/A errors.
XLOOKUP: A lookup with a built-in "not found" value.
ARRAYFORMULA: Apply a formula across a whole range.

IFERROR