Enrollment for the FMVA™ Certification Program is now OPEN!

IFERROR Function

Returns a customized result when the result of a formula is an error

What is the IFERROR Function?

The IFERROR Function is categorized under Logical Functions. IFERROR belongs to a group of error-checking functions such as ISERR, ISERROR, IFERROR, and ISNA. The function will return a customized result when the result of the formula is an error and a standard result if there is no error.

In financial analysis, we need to deal with formulas and data. Often, we will get errors in cells with a formula. IFERROR is used to trap and handle errors produced by other formulas and functions. It handles errors such as #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.

For example, the formula in C2 is A2/B2, where:

 

IFERROR Function

 

Instead of the resulting error, we can use IFERROR to return a customized message such as “Invalid input.”

 

Formula

=IFERROR(value,value_if_error)

 

The IFERROR Function uses the following arguments:

  1. Value (required argument) – It is the expression or value that needs to be tested. It is generally provided as a cell address.
  2. Value_if_error (required argument) – The value that will be returned if the formula evaluates to an error.

To learn more, launch our free Excel crash course now!

 

How to use the IFERROR Function in Excel?

As a worksheet function, IFERROR can be entered as part of a formula in a cell of a worksheet.

To understand the uses of the function, let’s consider a few examples:

 

Example 1

Suppose we are given the following data:

 

IFERROR Function - Example 1

 

Using the ISERROR formula, we can remove these errors. We will put a customized message – “Invalid data.” The formula to be used is:

 

IFERROR Function - Example 1a

 

We will get the result below:

 

IFERROR Function - Example 1b

 

Example 2

The most common use of IFERROR function is with VLOOKUP function, where it is used to inform users that the value they are looking out for is not there.

Suppose we operate a restaurant and maintain an inventory of a few vegetables and fruits. Now we compare it with the list of items we want according to the day’s menu. The results look like this:

 

IFERROR Function - Example 2

 

Instead of the #N/A error message, we can input a customized message. It will look better when we are using a long list.

The formula to use will be:

 

IFERROR Function - Example 2a

 

We will get the result below:

 

IFERROR Function - Example 2b

 

Suppose we are given monthly sales data for the first quarter. We create three worksheets – Jan, Feb, March.

Now we wish to find out the sales figures of certain order ids. As data would be derived by VLOOKUP from three sheets, using IFERROR, we can use the following formula:

=IFERROR(VLOOKUP(A2,’JAN’!A2:B5,2,0),IFERROR(VLOOKUP(A2,’FEB’!A2:B5,2,0),IFERROR(VLOOKUP(A2,’MAR’!A2:B5,2,0),”not found”)))

 

Example 3

Let’s see how to use IFERROR with an array formula. When we supply an array formula or expression that results in an array in the value argument of the IFERROR function, it will return an array of values for each cell in the specified range.

Suppose we are given the following data:

 

IFERROR Function - Example 3

 

To calculate total quantity, we need to divide Total Amount with Price per unit. We can use an array formula, which divides each cell in the range B2:B4 by the corresponding cell of the range C2:C4, and then adds up the results:

=SUM($B$2:$B$5/$C$2:$C$5)

The formula will work perfectly unless the divisor range does not contain zeros or empty cells. If there is at least one 0 value or blank cell, the #DIV/0! error is returned:

 

IFERROR Function - Example 3a

 

In order to fix the above error, we can use the IFERROR Function. The new formula would be:

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

 

IFERROR Function - Example 3b

 

What the formula did here was that it divided the value in column B by a value in column C in each row (50000/10, 50000/100, 50000/20 and 0/0) and return an array of results {5000; 500; 2500; & #DIV/0!}. Thus, IFERROR function identified all #DIV/0! errors and replaced them with zeros. The SUM function then adds up the values in the resulting array {5,000; 500; 2,500; 0} and gives the final result (8000).

 

Things to remember about the IFERROR Function

  1. The IFERROR Function was introduced in Excel 2007 and is available in all subsequent Excel versions.
  2. If the value argument is a blank cell, it is treated as an empty string (”’) and not an error.
  3. If value is an array formula, IFERROR returns an array of results for each cell in the range specified in value.

 

Click here to download the sample Excel file

 

Additional resources

Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional resources:

Free Excel Tutorial

To master the art of Excel, check out CFI's FREE Excel Crash Course, which teaches you how to become an Excel power user.  Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.  

Launch CFI’s Free Excel Course now to take your career to the next level and move up the ladder!