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 value.
Note: the Iferror function is new to Excel 2007, so is not available in earlier versions of Excel.
The syntax of the function is:
|value||-||The initial value or expression that should be tested|
|value_if_error||-||The value or expression to be returned if the supplied value argument returns an error.|
The following spreadsheet shows two simple examples of the Excel Iferror function.
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:
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 returned.
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 (and later versions of Excel), the above action can be performed much more efficiently and neatly, by using the Iferror function. The new formula is written as:
The following spreadsheet shows two further examples of the Excel Iferror function. The formulas are shown in the top spreadsheet and the results are shown in the spreadsheet below.
Further information and examples of the Excel Iferror function are provided on the Microsoft Office website.
Also, an example of using the Excel Iferror function to hide errors within conditional formatting can be viewed on the Microsoft Support website.