Excel Iferror Function

Basic Description

The Excel Iferror function tests if an initial supplied value (or expression) returns an error, and if so, returns a second supplied argument; Otherwise the function returns the initial tested value.

The Iferror function is new to Excel 2007, so is not available in earlier versions of MS Excel

The syntax of the function is:

IFERROR( value, value_if_error )

Where the arguments are as follows :


value - The initial value or expression that should be tested
value_if_error - Value or expression to be returned if the initial value argument returns an error.


Iferror Function Example 1

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

 Formulas:
  A B C
1 1 2 =IFERROR( A1 / B2, 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

In the first example (in cell C1), the initial value, A1/B1 returns the value 0.5. This is not an error and so this value is returned by the Iferror function.

In the second example (in cell C2), the initial value, A2/B2 returns the DIV/0! error. Therefore, the Iferror function returns the value_if_error, which is 0.


Iferror and Vlookup - Improvement Compared to Excel 2003

The Excel Iferror function was introduced in Excel 2007.

Previously, in Excel 2003, many users of the Excel Vlookup function would combine this with the If function and the Iserror function, to test for an error, and return an appropriate result. This is shown in the following formula:

IF( ISERROR( VLOOKUP( ... ) ), "not found", VLOOKUP( ... ) )

the above formula checks if the Vlookup function returns an error, and if so, returns the text "not found". Otherwise the value returned by the Vlookup is used.

Although this formula is long and inefficient (as it requires 2 seperate calls to the Vlookup function), it is useful because it helps to keep your spreadsheet cells tidy and free from error messages.

In Excel 2007 or 2010, the above action can be performed much more efficiently and neatly, by using the Iferror function. The new formula is written as:

IFERROR( VLOOKUP( ... ), "not found" )

This is illustrated in example 2 below.


Iferror Function Example 2

The following spreadsheet shows two examples of the Excel Iferror function. The formulas are shown in the top spreadsheet and the results are shown in the spreadsheet below.

 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 Example

An example of using the Excel Iferror function within conditional formatting, to hide errors, can be viewed on the Microsoft Support website.

Return to the Excel Logical Functions Page

Return to the List of All Built-In Excel Functions

Valid XHTML 1.0 Transitional Valid CSS!
Disclaimer   Privacy Policy
Copyright © 2008-2014 ExcelFunctions.net