IFERROR in Excel: what it does and how to use it

IFERROR runs a formula and, if it comes back as an error, shows what you asked for instead.

Syntax

IFERROR(value, value_if_error)

When to use it

Wrapping a division that might hit a zero, or a lookup that might not find anything, so the sheet reads cleanly rather than filling with error codes.

What trips people up

It hides every kind of error, including the ones caused by a genuine mistake in the formula. Get the formula right first, then wrap it, or you may never see that something is wrong.

Watch it built, step by step

Show a blank instead of an error — Keep a sheet clean when a formula fails
Work out a percentage change — This year against last year
Pull a price in from another sheet — Matching on a product code

Questions

What is the difference between IFERROR and IFNA?

IFERROR catches every error; IFNA catches only #N/A. For lookups IFNA is safer, because a real mistake such as a mistyped name still shows.

Should I wrap every formula in IFERROR?

No. It hides genuine problems as well as expected ones. Use it where an error is a normal result, such as a lookup that may not find a match.

How do I show 0 instead of an error?

Give 0 as the second argument: =IFERROR(A2/B2,0).

Related functions

VLOOKUP — VLOOKUP searches down the first column of a range for a value, and returns something from a column further to the right on the same row.
XLOOKUP — XLOOKUP takes a value, looks for it in one column, and returns the matching item from another.
IF — IF tests something and gives one answer when the test passes and another when it fails.

Formula Steps Watch how Excel formulas are built. Download the sheet.

Settings: separator, Excel version

Argument separator

Set by your computer's language, not by the file. A frequent cause of a correct-looking formula being rejected.

Excel version

XLOOKUP, IFS, TEXTBEFORE and TEXTJOIN need Microsoft 365 or 2021; older versions return #NAME? instead. Choosing the older one switches every walkthrough to a formula that works there.