This Tutorial Covers
- Excel IFERROR Function – An Overview
- Excel IFERROR Function – Examples
- Excel IFERROR Function – VIDEO
Excel IFERROR Function – An Overview
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
Using the IFERROR function, you can specify what you want the formula to return instead of the error. If the formula does not return an error, then its own result is returned.
IFERROR Function Syntax
- 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.
Example 4 – Using Nested IFERROR with VLOOKUP
Sometimes when using VLOOKUP, you may have to look through the fragmented table of arrays. For example, suppose you have the sales transaction records in 2 separate worksheets and you want to look-up an item number and see it’s value.
Doing this require you to use nested IFERROR with VLOOKUP.
Suppose you have a dataset as shown below:
In this case, to find the score for Grace, you need to use the below nested IFERROR formula:
This kind of formula nesting ensure that you get the value from either of the table and any error returned is handled.
Note that in case the tables are on the same worksheet, however, in a real-life example, it likely to be on different worksheets.