Excel IFERROR function

When applying formulas in Excel worksheet, some error values will be generated, to deal with the errors, Excel provide a useful function-IFERROR function. IFERROR function is used to return a custom result when a formula evaluates an error, and return a normal result when no error is occurred.

 Syntax:

The syntax for the IFERROR function in Excel is:

 Arguments:

• value: Required. A formula, expression, value, or cell reference to check for an error.
• value_if_error: Required. A specific value to return if an error is found. It can be an empty string, text message, numeric value, another formula or calculation.

 Return:

Returns the specific value for the error values.

 Examples:

Example 1: IFFEROR function to return blank cell or a custom text instead of error value

For example, you have a list of below data, to calculate the average price, you should use Sale / Unit. But, if the Unit is 0 or blank cell, errors will be displayed as below screenshot shown:

Now, I will use a blank cell or other text string to replace the error values:

=IFERROR(B2/C2, "") (This formula will return a blank instead of the error value)

=IFERROR(B2/C2, "Error") (This formula will return a custom text “Error” instead of the error value)

Example 2: IFERROR with Vlookup function to return “Not Found” instead of error values

Normally, when you apply the vlookup function to return the corresponding value, if your matching value is not found, you will get an #N/A error value as following screenshot shown:

Instead of displaying the error value, you can use a “Not Found” text to replace it. In this case, you can wrap the Vlookup formula into the IFERROR function like this: =IFERROR(VLOOKUP(…),"Not found")

Please use the below formula, and then a custom text “Not Found” will be returned to instead the error value while the matching value is not found, see screenshot:

Example 3: Using Nested IFERROR with Vlookup function

This IFERROR function also can help you to deal with multiple vlookup formulas, for example, you have two lookup tables, now need to look for the item from these two tables, to ignore the error values, please use the nested IFERROR with Vlookup as this:

Example 4: IFERROR function in array formulas

Let's say, if you want to calculate Total Quantity based on the list of Total Price and Unit Price, this can be done by using an array formula, which divides each cell in the range B2:B5 by the corresponding cell of the range C2:C5, and then adds up the results as using this array formula: =SUM($B$2:$B$5/$C$2:$C$5).

Note: If there is at least one 0 value or blank cell in the used range, the #DIV/0! error is returned as below screenshot shown:

To fix that error, you can wrap the IFERROR function into the formula as this, and please remember press Shitf + Ctrl + Enter keys together after entering this formula: