Summary

The Excel IFERROR function returns a custom result when a formula generates an error, and a standard result when no error is detected. IFERROR is an elegant way to trap and manage errors without using more complicated nested IF statements.

Purpose 

Trap and handle errors

Return value 

The value you specify for error conditions.

Syntax

=IFERROR(value,value_if_error)
  • value - The value, reference, or formula to check for an error.
  • value_if_error - The value to return if an error is found.

How to use 

The IFERROR function is used to catch errors and return a more friendly result or message when an error is detected. When a formula returns a normal result, the IFERROR function returns that result. When a formula returns an error, IFERROR returns an alternative result. IFERROR is an elegant way to trap and manage errors. The IFERROR function is a modern alternative to the ISERROR function.

Use the IFERROR function to trap and handle errors produced by other formulas or functions. IFERROR checks for the following errors: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.

Example #1

In the example shown, the formula in E5 copied down is:

=IFERROR(C5/D5,0)

This formula catches the #DIV/0! error that occurs when Qty is empty or zero, and replaces it with zero.

Example #2

For example, if A1 contains 10, B1 is blank, and C1 contains the formula =A1/B1, the following formula will catch the #DIV/0! error that results from dividing A1 by B1:

=IFERROR (A1/B1,"Please enter a value in B1")

As long as B1 is empty, C1 will display the message "Please enter a value in B1" if B1 is blank or zero. When a number is entered in B1, the formula will return the result of A1/B1.

Example #3

You can also use the IFERROR function to catch the #N/A error VLOOKUP throws when a lookup value isn't found. The syntax looks like this:

=IFERROR(VLOOKUP(value,data,column,0),"Not found")

In this example, the IFERROR function evaluates the result returned by VLOOKUP. If no error is present, the result is returned unchanged. However, if VLOOKUP returns an #N/A error, IFERROR catches the error and returns "Not Found". 

IFERROR or IFNA?

The IFERROR function is a useful function, but it is a rather blunt instrument that will trap all kinds of errors. For example, if a function is misspelled in a formula, Excel will return the #NAME? error and IFERROR will catch that error too, and return an alternate result. This can cause IFERROR to hide an important problem. In most cases, it makes more sense to use the IFNA function with VLOOKUP instead of IFERROR.

=IFNA(VLOOKUP(value,data,column,0),"Not found")

Unlike IFERROR, IFNA only traps the #N/A error.

Other error functions

Excel provides a number of error-related functions, each with a different behavior:

Notes

  • If value is empty, it is evaluated as an empty string ("") and not an error.
  • If value_if_error is supplied as an empty string (""), no message is displayed when an error is detected.
  • In Excel 2013+, you can use the IFNA function to trap and handle #N/A errors specifically.
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.