Skip to main content

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


The Best Office Productivity Tools

Kutools for Excel - Helps You To Stand Out From Crowd

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates  |  Delete Blank Rows  |  Combine Columns or Cells without Losing Data  |  Round without Formula ...
Super VLookup: Multiple Criteria  |  Multiple Value  |  Across Multi-Sheets  |  Fuzzy Lookup...
Adv. Drop-down List: Easy Drop Down List  |  Dependent Drop Down List  |  Multi-select Drop Down List...
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  Compare Columns to Select Same & Different Cells ...
Featured Features: Grid Focus  |  Design View  |  Big Formula Bar  |  Workbook & Sheet Manager | Resource Library (Auto Text)  |  Date Picker  |  Combine Worksheets  |  Encrypt/Decrypt Cells  |  Send Emails by List  |  Super Filter  |  Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters ...)  |  50+ Chart Types (Gantt Chart ...)  |  40+ Practical Formulas (Calculate age based on birthday ...)  |  19 Insertion Tools (Insert QR Code, Insert Picture from Path ...)  |  12 Conversion Tools (Numbers to Words, Currency Conversion ...)  |  7 Merge & Split Tools (Advanced Combine Rows, Split Excel Cells ...)  |  ... and more

Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...

Description


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.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations