Excel Iferror Function

Function Description

The Excel Iferror function receives two values (or expressions) and tests if the first of these evaluates to an error.

The syntax of the Iferror function is:

IFERROR( value, value_if_error )

Where the arguments are:

value - The first value or expression that should be tested.
value_if_error - The alternative value or expression, that is to be returned if the supplied value argument returns an error.

Note: the Iferror function is new to Excel 2007, so is not available in earlier versions of Excel.


Excel Iferror Function Examples

Example 1

The following spreadsheet shows two simple examples of the Excel Iferror function.

 Formulas:
  A B C
1 1 2 =IFERROR( A1 / B1, 0 )
2 1 0 =IFERROR( A2 / B2, 0 )
 Results:
  A B C  
1 1 2 0.5   - A1 / B1 produces no error so result 0.5 is returned
2 1 0 0   - A2 / B2 produces an error so the alternative value 0 is returned

Note that:


Example 2

In the following spreadsheet the Excel Iferror function is used with the Vlookup function.

If the Vlookup function successfully looks up a value, this is displayed in the cell; Otherwise, the text "not found" is displayed.

 Formulas:
  A B C D
1 Lookup List Jim's Class: =IFERROR( VLOOKUP( "Jim", A2:B6, 2, FALSE ), "not found" )
2 Beth Class 1 Mary's Class: =IFERROR( VLOOKUP( "Mary", A2:B6, 2, FALSE ), "not found" )
3 Bob Class 2    
4 Alf Class 2    
5 Jim Class 3    
6 Ann Class 3    
 Results:
  A B C D
1 Lookup List Jim's Class: Class 3
2 Beth Class 1 Mary's Class: "not found"
3 Bob Class 2    
4 Alf Class 2    
5 Jim Class 3    
6 Ann Class 3    

Further Iferror Function Examples

For further information and examples of the Excel Iferror, see the Microsoft Office website.

Also, an example of using the Excel Iferror function to hide errors within conditional formatting is provided on the Microsoft Support website.