The Excel IFERROR function returns a custom result when a formula generates an error, and a standard result when no error is detected. IFERROR is an elegant way to trap and manage errors without using more complicated nested IF statements.
Syntax
The syntax for the IFERROR function in Microsoft Excel is:
=IFERROR( formula, alternate_value )
Parameters or Arguments
formula
The formula or value that you want to test.
value_if_error
The alternate value that is returned if the formula results in an error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? or #NULL). Otherwise, the function will return the result of the formula if no error occurs.
Excel Iferror Function Examples
Example 1
The following spreadsheet shows two simple examples of the Excel Iferror function.
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.
Notes:
– If formula is empty, it is evaluated as an empty string (“”) and not an error.
– If value_if_error is supplied as an empty string (“”), no message is displayed when an error is detected.
– If IFERROR is entered as an array formula, it returns an array of results with one item for each cell in value.