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.
Notes:
- 1. The IFERROR function can deal with all error types which including #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, and #VALUE!.
- 2. If the value argument is a blank cell, it is treated as an empty string("") by IFERROR function.
- 3. If the value_if_error argument is supplied as an empty string (""), no message is displayed when an error is detected.
- 4. If value argument is an array formula, IFERROR returns an array of results for each cell in the range specified in value.
- 5. This IFERROR is available in Excel 2007 and all subsequent versions.
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:
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Supports Office/Excel 2007-2021 and 365 | Available in 44 Languages | Easy to Uninstall Completely
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
