How to Use the IFERROR Function in Excel (+ ISERROR)

The IFERROR function is a part of Excel’s logical function group.

It checks given formulas for any errors. Safe to say we use the IFERROR function to trap and handle errors in excel formulas.

IFERROR carries the same concept as the ISERROR function but with an addition of the IF function.

Let’s discuss how to use the IFERROR function alongside some other Excel functions.

So continue reading and download our free sample workbook here to practice as you read. ๐Ÿ˜‰

How to use IFERROR

The IFERROR function has two arguments:

=IFERROR(value, value_if_error)

  1. ‘value’- This is the expression (formula) that the function tests for any error.
  2. ‘value_if_error’- This is the expression we want to be returned if an error occurs.

Pro Tip!

The IFERROR function tests the following errors:

  • #N/A
  • #VALUE!
  • #REF!
  • #DIV/0!
  • #NUM!
  • #NAME?
  • #NULL!

When IFERROR checks a formula for any of the errors mentioned above – it returns the value we directed it to. This value is the one we specified for the value_if_error argument.

It’s time we use the IFERROR function for the following example in Excel.๐Ÿ™Œ

IFERROR data

The data above represents employees’ working days, salary, and the number of sales made by each of them.

Suppose you want to calculate the sales efficiency of each employee. To do so, we will divide each employee’s sales (Column D) and working days (Column B).

=D1/B1

Drag and drop the same formula to the whole list.

Simple calculation

Excel displays #DIV/0! error message in cells E3 and E5. (Because the dividend i.e. Sales is zero for E3 and E5).

Let’s do the same thing again, but this time with the IFERROR function.

We are enclosing the same above formula in the IFERROR function:

=IFERROR(D3/B3 , 0)

Sales efficiency using IFERROR

In place of the value_if_error argument, we have specified zero (o).

Instead of displaying an error message, excel returned zero. This is because we provided zero as the value_if_error.

You can even display a custom message using the IFERROR function.

Let’s modify the above formula in the following way:

=IFERROR(D3/B3,”On Leave”)

This time the value_if_argument is “On Leave”.

Display custom message

Once again, the function casts its magical spell on the error and provides us with a custom message in its place.๐ŸŽ‰

Now that you know what the IFERROR function is, why not deal with complicated situations?

IFERROR VLOOKUP formula example

With the VLOOKUP formula, we often face situations where the value we are looking for does not exist. In such cases, Excel returns the ‘#N/A’ error.

And it looks very nasty, we know. ๐Ÿ™„

Take a look at the following piece of data. Here, the data of employees of a company has been listed in columns.

IFERROR VLOOKUP data

Suppose you want to match employees’ attendance with the attendance column from the lookup table below.

Lookup table

Let’s try using the VLOOKUP formula.

=VLOOKUP(A3,A13:A15,1,0)

VLOOKUP formula

The list of present employees did not contain the names “Jason and Anna,”. And so, the result that VLOOKUP returns for these two employees are the ‘#N/A’ error.

But hold on! ๐Ÿคš We don’t want the very annoying #N/A error on our sheet. Instead, we want a simple “Absent” status for the employees not mentioned in the lookup table.

To do this, we will use nest the VLOOKUP function used above in the IFERROR function:

= IFERROR (VLOOKUP (A5,A13:A15,1,0),”Absent”)

IFERROR VLOOKUP example

Note that we have specified the status “Absent” as the value_if_error. It must always be enclosed in double quotation marks.

The IFERROR function returned the value “Absent” in place of the #N/A! error this time.

Kasper Langmann, Microsoft Office Specialist

How to use ISERROR

The ISERROR Excel function has a single argument in its syntax.

=ISERROR(value)

Here ‘value’ represents the value or cell reference you want to test for an error. It only tells if there is or isn’t an error in the formula.

The Excel ISERROR function tests the same set of errors as the IFERROR function does.

Let’s put it into action. The following data represents the cost and units of products bought by a local grocery store.

ISERROR data

Say you want to calculate the cost per unit of each product. To do so, you will perform a simple division (total cost/number of units).

But wait. Here’s what you get out of it.

Cost per unit (with error)

For cells D4 and D6, we only have a ‘#DIV/0!’ error.

You surely don’t want to see such terms in your sheet.

To know beforehand if your formula is an erroneous one or not, use the ISERROR function as follows:

=ISERROR(B2:B7/C2:C7)

Cost per unit using ISERROR

Drag and drop the above formula to the whole list to find a mix of Boolean results (True/False).

What does this mean? The ISERROR function tests a value to be an error or not.

If the cell reference (referred to as value) contains an error, the ISERROR function would return true. If it is not an error, the result would be false.

Pro Tip!๐Ÿ’ก

Try using an array formula when you have a long list of data to check. This saves you time and prevents unwanted errors by checking all the formulas in one go.

IF ISERROR formula example

In the above example, the ISERROR function in Excel checks the value and returns true or false as the result.

Like the IFERROR function, you might want to replace these values with some other specific value.

As the ISERROR doesn’t allow for this by default, you can still do so by nesting it in the IF function.

The IF function does logical testing and works the same way as the IFERROR function. Let’s write it as follows:

=IF(ISERROR(B2:B8/C2:C8),”On Route”,B2:B8/C2:C8)

Pro Tip!

Here’s how both these functions work:

  • The first argument is a logical test. We have nested the ISERROR in its place which will perform a logical test on the value and return True/False.
  • The second argument specifies the value (“On Route”) to be returned by the IF function if the logical test turns out true.
  • The third argument specifies the value (the result of the division function) to be returned by the IF function if the logical test turns about false.
IF ISERROR example

See how the two functions worked together to give us our desired results.

It gave division results for the cells where the logical test turned true. And the value “On Route” for the cells where the logical test turned false.

Note that in the above example, we selected a blank cell in the provided reference.

The function automatically divided it by zero and gave a true result.

Blank cell

Pro Tip!

Instead of using the IF ISERROR function, use the IFERROR function. It will perform the same action and save complications. ๐Ÿ’ช

IFERROR vs ISERROR

We have seen in the above examples that both of these functions are used for identifying an error. But they are still different. For example:

  • The ISERROR function only tells whether the value is an error or not.
  • It only returns Boolean values (True/Fales) as results.
  • On the other hand, the IFERROR function checks a formula for any error. It further allows users to define a value to replace the error (if there exists any).

Both functions come in handy. And you can use them with other formulas/functions such as the SUM function, VLOOKUP function, etc.

But for efficient results, the IFERROR function is a better option. โœ”

Thatโ€™s it โ€“ Now what?

This step-by-step guide teaches you the use of IFERROR and ISERROR functions. Not only that, but it also explains how they may be nested into other functions for the desired results.

Both of these functions are of great value, but the IFERROR function tops the list. โœŒ

Although these are fundamental functions, do know that Excel has much more than this to offer. Some principal functions of Excel include the VLOOKUP, IF, and SUMIF functions.

Want to learn them? Register for my 30-minute free email course to master these and much more.

Other resources

If you’re still reading, you probably want more knowledge.

Want to learn about the ISERROR function in more detail? Learn it here.

Or are you wondering where and why these errors even pop up in the first place? Learn and fix this here.