Use the IFERROR function to trap and handle errors produced by other formulas or functions. IFERROR checks for the following errors: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.
Example #1
In the example shown, the formula in E5 copied down is: This formula catches the #DIV/0! error that occurs when Qty is empty or zero, and replaces it with zero.
Example #2
For example, if A1 contains 10, B1 is blank, and C1 contains the formula =A1/B1, the following formula will catch the #DIV/0! error that results from dividing A1 by B1: As long as B1 is empty, C1 will display the message “Please enter a value in B1” if B1 is blank or zero. When a number is entered in B1, the formula will return the result of A1/B1.
Example #3
You can also use the IFERROR function to catch the #N/A error thrown by VLOOKUP when a lookup value isn’t found. The syntax looks like this: In this example, when VLOOKUP returns a result, IFERROR functions that result. If VLOOKUP returns #N/A error because a lookup value isn’t found, IFERROR returns “Not Found”.
IFERROR or IFNA?
The IFERROR function is a useful function, but it is a blunt instrument since it will trap many kinds of errors. For example, if there’s a typo in a formula, Excel may return the #NAME? error, but IFERROR will suppress the error and return the alternative result. This can obscure an important problem. In many cases, it makes more sense to use the IFNA function, which only traps the #N/A error.
Other error functions
Excel provides a number of error-related functions, each with a different behavior:
The ISERR function returns TRUE for any error type except the #N/A error. The ISERROR function returns TRUE for any error. The ISNA function returns TRUE for #N/A errors only. The ERROR.TYPE function returns the numeric code for a given error. The IFERROR function traps errors and provides an alternative result. The IFNA function traps #N/A errors and provides an alternative result.
Notes
If value 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. In Excel 2013+, you can use the IFNA function to trap and handle #N/A errors specifically.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
