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:
 ABC
112=IFERROR( A1 / B1, 0 )
210=IFERROR( A2 / B2, 0 )
 Results:
 ABC 
1120.5  - A1 / B1 produces no error so result 0.5 is returned
2100  - 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:
 ABCD
1Lookup ListJim's Class:=IFERROR( VLOOKUP( "Jim", A2:B6, 2, FALSE ), "not found" )
2BethClass 1Mary's Class:=IFERROR( VLOOKUP( "Mary", A2:B6, 2, FALSE ), "not found" )
3BobClass 2  
4AlfClass 2  
5JimClass 3  
6AnnClass 3  
 Results:
 ABCD
1Lookup ListJim's Class:Class 3
2BethClass 1Mary's Class:"not found"
3BobClass 2  
4AlfClass 2  
5JimClass 3  
6AnnClass 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.