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

## Practice Workbook

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

## IFERROR Function in Excel

### 1. Basics of IFERROR Function

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

### 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 Function

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

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

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

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.

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.

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.

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**

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

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.

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

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.

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.

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.

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

**Similar Readings**

**AND Function in MS Excel****How to Use FALSE Function in Excel (With 5 Easy Examples)****Use Excel SWITCH Function (5 Examples)****How to Use Excel XOR Function (5 Suitable Examples)****How to Use IFNA Function in Excel (2 Examples)**

### 3. Quick Notes

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

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.

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

.

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

You can also set expression in the *value_if_field*.

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

- You can directly write an expression inside the
**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.

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.