Function: IFERROR

by | Jan 23, 2019 | Excel Formulas

IFERROR-01

The IFERROR function evaluates a formula and returns a specified value if the formula generates an error. Otherwise, the function returns the result of the formula. In this guide, we’re going to show you how to use the IFERROR function and also go over some tips and error handling methods.

Supported Excel versions

  • (Windows) Excel 2007 and above
  • (Mac) Excel for Mac 2011 and above
  • Web and mobile versions

Syntax

IFERROR(value, value_if_error)

Arguments

value The formula or the reference which is evaluated for an error.
value_if_error The return value that if the formula evaluates to an error.


Examples

In this example we've used named ranges to make the formulas easier to read. This is not required.

Example 1

=IFERROR(VLOOKUP(Search_Value,Table,2,0),"Not found")
evaluates the formula from its first argument, and returns the string "Not found". Since the search value, "Mew", doesn’t exist in the table, the VLOOKUP formula returns an #N/A error.

IFERROR-01

Example 2

=IFERROR(VLOOKUP(Search_Value_2,Table,2,0),"Not found")
formula evaluates the formula from its first argument and returns the result of the formula, 340. Since the search value, "Staryu", exists in the table, the VLOOKUP formula returns the value form the 2nd column of the range.

IFERROR-02

Example 3

=IFERROR(SUM(Total)/Divisor,IF(Divisor=0,"Divisor should not be 0","Please check the values in Total column"))
evaluates the formula from its first argument and returns the result of the formula used in its 2nd argument as "Divisor should not be 0", because dividing a value to 0 throws #DIV/0! error. When the first argument of the IFERROR function results in an error, the formula from the second argument is used as the return value.

IFERROR-03

Download Workbook

Tips

  • The IFERROR checks for the following errors: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.
  • If a reference cell is empty, the IFFERROR function treats it as an empty string ("").
  • You can also use the IFNA function to handle #N/A errors.
  • You can use the ISERROR and the ISERR functions to detect errors without replacing them.