FormulaGym
XP 0
Streak 0

The IFERROR function in Excel

IFERROR swaps an error for something readable.

What IFERROR does

It evaluates a formula. If the formula works, you get its result. If it returns an error such as #N/A or #DIV/0!, you get the replacement you chose instead. It is most often wrapped around a lookup so a missing match shows Not found.

Syntax

=IFERROR(value, value_if_error)
value
The formula to try, for example a lookup.
value_if_error
What to show if it returns an error, such as "Not found" or 0.

Worked example

ABCDEF
1IDProductPriceLook up IDPrice
21001Notebook81999Not found
31004Desk lamp25
41007Backpack40
51010Headphones60
F2 =IFERROR(VLOOKUP(E2,A2:C5,3,FALSE),"Not found")

ID 1999 is not in the table, so VLOOKUP would return #N/A. IFERROR shows Not found instead.

Now you try

A fresh exercise every time. Press Another exercise as often as you like.

Where you will use it

Data cleanup

Tidy up reports that show error codes.

Finance

Show 0 instead of a divide-by-zero error.

Sales & Ops

Show a clear message when a lookup finds nothing.

Common mistakes

  • Hiding real problems. Wrap only the part where an error is expected, so genuine mistakes still show up.
  • Wrapping an entire long formula, which masks typos and wrong references as well.
  • Returning an empty string and then doing maths on it. Text in a calculation causes a #VALUE! error.

The IFERROR course

  1. 1
    Handle a missing matchFree practice below
    Practise
  2. 2
    Blank instead of an errorComing soon
  3. 3
    Fallback lookupsComing soon
  4. 4
    Spot errors you should not hideComing soon
  5. 5
    Clean up a messy reportComing soon

Common questions

What is the difference between IFERROR and IFNA?

IFNA only catches #N/A errors, so it hides fewer genuine problems. IFERROR catches every kind of error.

What can I show instead of an error?

Text such as Not found, an empty string, or 0. Pick whatever suits the report.

Related functions