Syntax. IFERROR(value,value_if_error)
Definition. This function returns the second argument if the first argument results in an error. This function was introduced in Excel 2007.
Arguments
value (required). Any value or expression
value_if_error (required). The value that is returned if the value argument generates an error. The errors are #NA!
, #VALUE!
, #REF!
, #DIV/0!
, #NUM!
, #NAME?
, or #NULL!
. If no error exists, the value of the first argument is returned.
Background. Use the IFERROR() function to test values and formulas based on conditions.
If the value or value_if_error argument refers to an empty cell, the function treats this cell as a cell containing an empty string (“”).
If value is an array formula, the IFERROR() function returns a result array for each cell referred to by the value argument.
Because this function can be used instead of a combination of the IF() function and certain error functions, it results in shorter and clearer formulas.
Example. Assume that you have created a list with birthdays (or order numbers, address information, phone numbers, or something similar) and want to access this information using the VLOOKUP() function. Figure 9-5 shows an example.
=VLOOKUP(B44,D44:E46,2,FALSE)
in cell B45, you get the #N/A
error that is especially annoying when you are printing. In this case, you can use the IF() function in B46 instead of the simpler formula in B45.
=IF(ISERROR(VLOOKUP(B44,D44:E46,2,FALSE)),"not found", VLOOKUP(B44,D44:E46,2,FALSE))
This formula is somewhat complicated because of the redundant portion.
VLOOKUP(B44,D44:E46,2,FALSE)
The formula
=IFERROR(VLOOKUP(B44,D44:E46,2,FALSE),"not found")
is much more concise. In other situations, you might want to use interim values and ignore errors.