How to use IFERROR Function in Excel (3 Examples)

There are several logical functions in Excel to perform various logical operations easily and swiftly. Today we are going to show you how to use the IFERROR function. For the session, we are using Excel 2019, feel free to choose yours (at least Excel 2007).

Sheet - Excel IFERROR Function

Practice Workbook

You are welcome to download the practice workbook from the link below.

IFERROR Function in Excel

1. Basics of IFERROR

IFERROR is categorized under the Logical functions in Excel. It returns an alternate value if a formula results in an error.

IFERROR - Excel IFERROR Function

Summary

Returns value_if_error if the expression is an error and value of the expression itself otherwise.

Syntax

IFERROR(value,value_if_error)

Arguments

value: The argument that is checked for an error.

value_if_error: The value to return if the formula evaluates to an error.

Versions

Workable in Excel version from Excel 2007.

2. Use of IFERROR

I. Basic Error Elimination

The basic use of the IFERROR function is the elimination of any type of error (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!) that occurs in an expression.

Let’s imagine a scenario where we have few items listed with their quantity and total price.

Divide - Excel IFERROR Function

We are set to find the ratio of cost and quantity. Divide cost by quantity.

Divide All - Excel IFERROR Function

Here we have divided the total cost by Quantity for each of the rows. A couple of the rows in the Quantity column are 0. That’s why a couple of rows in the Ratio column became #DIV/0!

To eradicate the error, we need to use IFERROR.

Divide with IFERROR - Excel IFERROR Function

Here we have set the expression within IFERROR as the value parameter. And 0 in the value_if_error field.

There was no error in these values so it returned the value of this division expression.

Compare division results - Excel IFERROR Function

Here earlier we found #DIV/0! error, but this time didn’t return any error but returned 0 instead.

Do the same for the rest of the rows.

Divide with IFERROR - results - Excel IFERROR Function

Here we have found the division result or 0 for error.

II. Usage in Array Formula

The IFERROR formula can be used in the array formula.

To show you examples we have introduced a dataset for few products with their unit price.

Array formula dataset- Excel IFERROR Function

The Total column stored the total bill for each product.

To calculate Total Quantity, we can use the array formula, where we will divide each cell in the range C4:C7 by the corresponding cell of the range D4:D7, and then adds up the results.

=SUM(C4:C7/D4:D7)

This is an array formula so we need to press CTRL + SHIFT + ENTER

Array formula sum - Excel IFERROR Function

The formula works fine as long as the divisor range does not have zeros or empty cells. If there is at least one 0 value or blank cell, the #DIV/0! error is returned.

To fix this error, we need to divide it within the IFERROR function.

=SUM(IFERROR(C4:C7/D4:D7,0))

Array Formula with IFERROR - Excel IFERROR Function

This formula divided the value in column C by a value in column D in each row ($100/$5, $200/$2, $60/$4 and 0/0) and return the array of results {20; 100; 15; #DIV/0!}.

The IFERROR function catches #DIV/0! errors and replaces them with zeros. And then, the array would be {20; 100; 15; 0}. 

Then the SUM function adds up the values in the resulting array and provides the final output.

III. Use with Lookup Function

You can use the IFERROR function along with the lookup function.

To show you examples we have introduced a dataset of few employees with their addresses and salaries.

Lookup dataset - Excel IFERROR Function

We will find the salary for the Employee ID. 

Since we are searching within columns, we will use VLOOKUP. 

VLOOKUP(lookup_value,lookup_array,column_num,match_type)

VLOOKUP - Excel IFERROR Function

Here our lookup_value was in the D11 cell. The lookup_array was B3:E8. The Salary has been stored as the 4th column in the table.

0 for the Exact Match. We have found the salary for the employee having C001 id.

Let’s change the lookup_value in our example.

VLOOKUP error - Excel IFERROR Function

Here we have set C006 in the cell which is used as the holder of our lookup_value. This value was not been stored. So this provided an error in the output.

#N/A occurs when value is not available.

To eradicate the error, we need to use the IFERROR function.

Write the earlier VLOOKUP function in the IFERROR function.

VLOOKUP with IFERROR - Excel IFERROR Function

Here we have set the function in such a way that this would return 0 for error.

Provide a value from our list, this will return the value as expected.

IFERROR with lookup examples - Excel IFERROR Function

Like this, you can use the IFERROR function with other lookup functions such as; LOOKUP and HLOOKUP. 

3. Quick Notes

  1. So far, we have set a number in the value_if_error field of IFERROR. You can set any text string also in this field.

string as value_if_error - Excel IFERROR Function

Here we are going to divide 80 by 0 (using Cell Reference) inside IFERROR. In the value_if_error field, we have set “Can’t Divide”.

When the expression provides an error (obviously there will be an error in this case), this will trigger to return this text.

String in value_if_error - Excel IFERROR Function

You can also leave the string without writing. We mean just use two double quotes ("").

empty string - Excel IFERROR Function

Here we have just written "", nothing inside the double-quotes. Excel assumes this as an Empty String (not BLANK).

Whenever an error will occur, this will show nothing (see the image below).

empty string result-Excel IFERROR Function

You can also set expression in the value_if_field.

Expression in if_error - Excel IFERROR Function

Here we are trying to add 4 with hello, and if this occurs any error function will add 4 and 5.

expression result - Excel IFERROR Function

  1. You can directly write an expression inside the IFERROR function.

Direct input in IFERROR - Excel IFERROR Function

Here we have inserted a division operation (70/0) directly in the value field and a text string “Error Value” in the value_if_error field.

Result of direct input - Excel IFERROR Function

This worked, we have found the correct output.

Conclusion

That’s all for today. We have tried showing how you can use the IFERROR function in different circumstances. You can use the function solo or with the combination of any other functions to eradicate error and generate value or human-understandable messages. Hope you will find this helpful.

Feel free to comment if anything seems difficult to understand. Let us know any of your IFERROR function-related scenarios where you have stuck, we are ready to help.

shakil

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo