How to Use Excel IFERROR Function (Examples + Video)

Excel IFERROR Function Overview

EXCEL IFERROR FUNCTION

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.

Syntax

=IFERROR(value, value_if_error)

Input Arguments

  • 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!.

Additional Notes

  • 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.

excel-iferror-function-divided-by-zero

In this case, you can use the following formula to return blank instead of the ugly DIV error.

=IFERROR(A1/A2,””)

excel-iferror-function-blank

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.

=IFERROR(A1/A2,”Error”)

excel-iferror-function-returns-error

Note: If you are using Excel 2003 or a prior version, you will not find the IFERROR function in it. In such cases, you need to use the combination of IF function and ISERROR function.

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).

excel-iferror-function-vlookup

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.

=IFERROR(VLOOKUP(D2,$A$2:$B$12,2,0),”Not Found”)

excel-iferror-function-vlookup-not-found

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.

excel-iferror-function-error-when-div-by-0

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.

excel-iferror-function-comma

Excel IFERROR Function – VIDEO