Logic and error handling

Excel IFERROR Function: Replace Errors With Something Readable

Runs a formula and returns your own value instead if it produces an error.

IFERROR wraps a formula and catches whatever goes wrong with it. If the formula works, you get its result; if it produces any error at all, you get the fallback you specified instead. It exists because a spreadsheet full of #DIV/0! and #N/A is unreadable, and because those errors spread — one #N/A in a column makes the SUM at the bottom of it an #N/A too.

The classic uses are division where the denominator might be zero, and lookups where the value might genuinely be missing. Both are situations where the error is expected and meaningless to the reader, and replacing it with a blank or a short label makes the sheet usable.

The danger is that IFERROR catches everything, including mistakes you would rather know about. A typo in a range name produces #NAME?, and IFERROR will quietly replace it with "Not found" as though it were a legitimate miss. When you specifically expect a lookup to fail, IFNA is the safer choice — it catches only #N/A and lets real errors through.

Syntax

=IFERROR(value, value_if_error)

Arguments

value
Required
The formula to attempt. Usually a division or a lookup.
value_if_error
Required
What to return if it errors. Often "" for a blank, or a short label like "Not found".

The example data

Headers in row 1, data in A2:C5.

ABC
1RepSalesTarget
2Alice12401000
3Bruno3851000
4Chen21001500
5Dana9400

Worked examples

=B5/C5

Result: #DIV/0!

Dana's target is 0, so this division fails. Left alone, it will poison any total built on the column.

=IFERROR(B5/C5, "No target")

Result: No target

The same division, with the error replaced by something a reader understands.

=IFERROR(B2/C2, "No target")

Result: 1.24

When the formula works, IFERROR is invisible — it returns the result unchanged.

=IFERROR(VLOOKUP("Eve", A2:C5, 2, FALSE), 0)

Result: 0

A missing lookup returning 0 rather than #N/A, so the column can still be summed.

Now practise it

Reading about a formula is not the same as writing one. Open this function's exercise and type it into a real grid — you get instant feedback on exactly which cell is wrong and why.

Open the exercise: IFERROR Function

Common errors and how to fix them

A real mistake is being hidden

Why it happens: IFERROR catches every error type, so a broken range reference or a misspelled function name is silently replaced by your fallback.

How to fix it: Use IFNA when you only mean to catch a failed lookup. Remove the IFERROR temporarily when a formula gives an unexpected result — the underlying error is usually the explanation.

Totals look wrong after wrapping

Why it happens: The fallback is text ("Not found") sitting in a numeric column, so SUM ignores those rows.

How to fix it: Return 0 rather than text when the column feeds arithmetic, and use text only in columns meant for reading.

#NAME?

Why it happens: The Excel version predates IFERROR, which arrived in Excel 2007.

How to fix it: Use the older =IF(ISERROR(formula), fallback, formula) pattern, which evaluates the formula twice but works everywhere.

Tips worth knowing

  • Return "" for a clean-looking report, but 0 when the column will be summed.
  • Prefer IFNA over IFERROR for lookups — it catches only the missing-value case and leaves genuine bugs visible.
  • XLOOKUP has a not-found argument built in, so it rarely needs wrapping at all.
  • Do not wrap a whole complicated formula out of habit. Wrap the specific part that can legitimately fail.

Frequently asked questions

What is the difference between IFERROR and IFNA?

IFERROR catches every error — #N/A, #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM! and #NULL!. IFNA catches only #N/A. For lookups, IFNA is usually correct: a missing value is expected, but a #REF! means your formula is broken and you want to see it.

Does IFERROR slow the spreadsheet down?

Barely. Unlike the old IF(ISERROR(...)) pattern, which evaluates the formula twice, IFERROR evaluates it once. On very large models the difference is measurable in the other direction — IFERROR is the faster of the two.

Should I wrap every formula in IFERROR?

No. Wrapping everything hides the errors that tell you something is genuinely wrong, and an error that appears months later in a report nobody checked is much more expensive than one that appears immediately. Wrap only where failure is expected and harmless.

Related functions

Guides that use it

Longer reads where this function does real work in a real sheet.

All functions