How to use the Excel IFERROR function


Excel IFERROR function

Summary

The Excel IFERROR function returns a custom result when a formula generates an error, and a standard result when no error is detected. IFERROR is an elegant way to trap and manage errors without using more complicated nested IF statements.

Return value

The value you specify for error conditions.

Syntax

=IFERROR (value, value_if_error)

Arguments

  • value – The value, reference, or formula to check for an error.
  • value_if_error – The value to return if an error is found.

Usage notes

The IFERROR function “catches” errors in a formula and returns an alternative result or formula when an error is detected.

Use the IFERROR function to trap and handle errors produced by other formulas or functions. IFERROR checks for the following errors: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.

Example #1

For example, if A1 contains 10, B1 is blank, and C1 contains the formula =A1/B1, the following formula will catch the #DIV/0! error that results from dividing A1 by B1:

=IFERROR (A1/B1,”Please enter a value in B1″)

As long as B1 is empty, C1 will display the message “Please enter a value in B1” if B1 is blank or zero. When a number is entered in B1, the formula will return the result of A1/B1.

Example #2

You can also use the IFERROR function to catch the #N/A error thrown by VLOOKUP when a lookup value isn’t found. The syntax looks like this:

In this example, when VLOOKUP returns a result, IFERROR functions that result. If VLOOKUP returns #N/A error because a lookup value isn’t found, IFERROR returns “Not Found”.

Notes

  • If value is empty, it is evaluated as an empty string (“”) and not an error.
  • If value_if_error is supplied as an empty string (“”), no message is displayed when an error is detected.
  • If IFERROR is entered as an array formula, it returns an array of results with one item for each cell in value.
  • In Excel 2013+, you can use the IFNA function to trap and handle #N/A errors specifically.

IFERROR formula examples

Excel formula: How to fix the #DIV/0! error

About the #DIV/0! error The #DIV/0! error appears when a formula attempts to divide by zero, or a value equivalent to zero. Like other errors, the #DIV/0! is useful, because it tells you there is something missing or…

Excel formula: Basic error trapping example

To catch errors that a formula might trigger in a worksheet, you can use the IFERROR function to display a custom message, or nothing at all. In the example shown, the formula in E5 is: =IFERROR(C5/D5,””)…

Excel formula: Extract multiple matches into separate rows

To extract multiple matches to separate cells, in separate rows, you can use an array formula based on INDEX and SMALL. In the example shown, the formula in E5 is: {=IFERROR(INDEX(names,SMALL(IF(groups=E$4,ROW(names)-…

Excel formula: How to fix the #NAME? error

The #NAME? error occurs when Excel can’t recognize something. Frequently, the #NAME? occurs when a function name is misspelled, but there are other causes, as explained below. Fixing a #NAME? error is usually just a…

Excel formula: Extract multiple matches into separate columns

To extract multiple matches to separate cells, in separate columns, you can use an array formula based on INDEX and SMALL. In the example shown, the formula in F5 is: {=IFERROR(INDEX(names,SMALL(IF(groups=$E5,ROW(…

Excel formula: How to fix the #NUM! error

The #NUM! error occurs in Excel formulas when a calculation can’t be performed. For example, if you try to calculate the square root of a negative number, you’ll see the #NUM! error. The examples below show formulas…

Excel formula: VLOOKUP without #N/A error

To hide the #N/A error that VLOOKUP throws when it can’t find a value, you can use the IFERROR function to catch the error and return any value you like. How the formula works When VLOOKUP can’t find a value in a…

Excel formula: How to fix the #NULL! error

The #NULL! error is quite rare in Excel, and is usually the result of a typo where a space character is used instead of a comma (,) or colon (:) between two cell references. Technically, the space character is the “…

Excel formula: How to fix the #VALUE! error

The #VALUE! error appears when a value is not the expected type. This can occur when cells are left blank, when a function that is expecting a number is given a text value, and when dates are evaluated as text by Excel…

Excel formula: Highlight cells that begin with

Note: Excel contains many built-in “presets” for highlighting values with conditional formatting, including a preset to highlight cells that begin with specific text. However, if you want more flexibility, you can use…

Excel formula: Multiple chained VLOOKUPs

If you need to perform multiple lookups sequentially, based on whether the earlier lookups succeed or not, you can chain one or more VLOOKUPs together with IFERROR. In the example shown, the formula in L5 is: =…

Excel formula: How to fix the #REF! error

About the #REF! error The #REF! error occurs when a reference is invalid. In many cases, this is because sheets, rows, or columns have been removed, or because a formula with relative references has been copied to a…

Excel formula: How to fix the #N/A error

About the #N/A error The #N/A error appears when something can’t be found or identified. It is often a useful error, because it tells you something important is missing – a product not yet available, an employee name…

Related videos

In this video, we’ll look at how to set up a VLOOKUP formula to avoid #N/A errors.

Trapping errors can make your spreadsheets more professional by making them less cluttered and more friendly to use. In this video, we’ll look at some ways to trap errors in a formula.

Because tables support structured references, you can learn a lot about a table with simple formulas. In this video, we’ll look at some formulas you can use to query a table.

Related functions

Excel ISERROR function

The Excel ISERROR function returns TRUE for any error type excel generates, including #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL! You can use ISERROR together with the IF function to test for errors and display a custom message, or…

Excel IFNA function

The Excel IFNA function returns a custom result when a formula generates the #N/A error, and a standard result when no error is detected. IFNA is an elegant way to trap and handle #N/A errors specifically without catching other errors.

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you’ll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You’ll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 200+ Excel Shortcuts

Hi – I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts. Read more.

Excel video training

Quick, clean, and to the point.

Learn more

Source

Leave a Reply

Your email address will not be published. Required fields are marked *