Excel IFERROR Function Overview
When to use Excel IFERROR Function
IFERROR function is best-suited to handle situations where the formulas return an error. Using the IFERROR function, you can specify what you want the formula to return instead of error. If the formula does not return an error, then its own result is returned.
What it Returns
It returns the specified value in case of error.
- value – this is the argument that is checked for the error. In most cases, it is either a formula or a cell reference.
- value_if_error – this is the value that is returned if there is an error. The following error types evaluated: #N/A, #REF!, #DIV/0!, #VALUE!, #NUM!, #NAME?, and #NULL!.
- If you use “” as the value_if_error argument, the cell displays nothing in case of an error.
- If the value or value_if_error argument refers to an empty cell, it is treated as an empty string value by the Excel IFERROR function.
- If the value argument is an array formula, IFERROR will return an array of results for each item in the range specified in value.
Excel IFERROR Function – Examples
Here are three examples of using IFERROR function in Excel.
Example 1 – Return Blank Cell Instead of Error
If you have functions that may return an error, you can wrap it within the IFERROR function and specify blank as the value to return in case of an error.
In the example shown below, the result in D4 is the #DIV/0! error as the divisor is 0.
In this case, you can use the following formula to return blank instead of the ugly DIV error.
This IFERROR function would check whether the calculation leads to an error. If it does, it simply returns a blank as specified in the formula.
Here, you can also specify any other string or formula to display instead of the blank.
For example, the below formula would return the text “Error”, instead of the blank cell.
Example 2 – Return ‘Not Found’ when VLOOKUP Can’t Find a Value
When you use the Excel VLOOKUP Function, and it can’t find the lookup value in the specified range, it would return the #N/A error.
For example, below is a data set of student names and their marks. I have used the VLOOKUP function to fetch the marks of three students (in D2, D3, and D4).
While if finds the names of first two students, it can’t find Josh’s name on the list and hence it returns the #N/A error.
Here, we can use the IFERROR function to return a blank or some meaningful text instead of the error.
Below is the formula that will return ‘Not Found’ instead of the error.
Example 3 – Return 0 in case of an error
If you don’t specify the value to return by IFERROR in the case of an error, it would automatically return 0.
For example, if I divide 100 with 0 as shown below, it would return an error.
However, if I use the below IFERROR function, it would return a 0 instead. Note that you still need to use a comma after the first argument.