Shakil Ahmed
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.
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.
” title=”