Home » Excel-Built-In-Functions » Excel-Logical-Functions » Excel-Iferror-Function
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.
- If the first supplied value does not evaluate to an error, this value is returned;
- If the first supplied value does evaluate to an error, the second supplied value is returned.
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:
- In the first example (in cell C1), the value argument, 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 value argument, A2/B2 returns the #DIV/0! error. Therefore, the Iferror function returns the value of the value_if_error argument, which is 0.
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.