Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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.

doc iferror function 1


 Syntax:

The syntax for the IFERROR function in Excel is:

=IFERROR(value, value_if_error)

 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:

doc iferror function 2

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)

doc iferror function 3 doc iferror function 4

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:

doc iferror function 5

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:

=IFERROR(VLOOKUP(D2,$A$2:$B$11,2,FALSE),"Not Found")

doc iferror function 6


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:

=IFERROR(VLOOKUP(G2,$A$2:$B$7,2,FALSE),IFERROR(VLOOKUP(G2,$D$2:$E$7,2,FALSE),"Not Found"))

doc iferror function 7


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:

doc iferror function 8

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:

=SUM(IFERROR($B$2:$B$5/$C$2:$C$5,0))

doc iferror function 9


Excel Productivity Tools

300 Advanced Features Help You To Stand Out From Crowd

Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300 powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) and save 70% time.

  • Designed for 1500 work scenarios, helps you solve 80% Excel problems.
  • Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
  • Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
  • 60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years.

Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Office Tab Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.