Return a custom result when a formula generates an error
=ISERROR(value)
Return a custom result when a formula generates any of the #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL! errors.
IFERROR is a simpler way to trap and handle errors than more complicated nested IF functions.
IFERROR syntax requires a value to test and a value to return if there is an error. The value argument can be a value, reference or formula.
IFERROR examples.
In the examples below, the IFERROR function is used to determine whether cells contain an error and return a custom value.
Use the IFERROR function to return a custom value if a formula or cell contains an error.
Syntax
IFERROR(value, value_if_error)
The IFERROR function requires 2 arguments:
- value. Required. The argument that is checked for an error.
- value_if_error. Required. The custom value to return if the formula evaluates to an error.
Try it now!
- Select the answer from the drop down lists in the grey cells.
Scope
IFERROR can be used on its own to determine whether a cell contains an error or combined within a formula to test a value prior to performing additional operations.
|
=IFERROR(123/0,"Divide by zero error!") || returns "Divide by zero error!". |
|
=IFERROR(C2/C3,"No value in C3") || returns "No value in C3" when C3 is empty. |
|
=IFERROR(VLOOKUP(Lookup_Value,MyRange,2,0),"Error in VLOOKUP") || returns "Error in VLOOKUP" if the lookup value or lookup_range cannot be found. |
Caveats
- IFERROR traps and handles any of the #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL! errors,
- If value is empty, it is handled like an empty text string ("") not an error.
- If value_if_error is empty, no value is displayed when an error is found.
- IFERROR will return an array of results with one item for each value in an array formula.
Related Functions
The following functions are also useful for error handling.
- IFNA to trap and handle #N/A errors specifically.
- ISBLANK returns the logical value TRUE if a cell is blank or empty.
- ISERR returns the logical value TRUE for any error type except the #N/A error.
- ISERROR returns the logical value TRUE for any error type.
- ISLOGICAL returns the logical value TRUE if the cell contains either a TRUE or FALSE statement.
- ISNA returns the logical value TRUE if a cell contains the #N/A error
- ISNONTEXT returns the logical value TRUE if the cell contains any value that is not text.
- ISNUMBER returns the logical value TRUE if the cell contains a number.
- ISREF returns the logical value TRUE if the cell contains a reference.
- ISTEXT returns the logical value TRUE if the cell contains any text.
Functions Category