Excel IFERROR Function – How To Use

IFERROR function provides a great way to handle errors in Excel formulas. Excel IFERROR function returns a custom result (which can be a text, cell reference, or another formula) when the expression enclosed inside it returns an error.

IFERROR helps you catch and address problems with your formulas so you can have clean and orderly spreadsheets. Alternatively, you can use nested IF statements to handle formula errors but nested IF statements are quite challenging to read and reduce the spreadsheets' maintainability. This is the reason IFERROR function is preferred for handling errors.

Excel IFERROR Function

Now, let's see the syntax of the IFERROR function.

IFERROR Function Syntax

The syntax of an IFERROR function is as follows:

=IFERROR(value, value_if_error)

'value' – This a required parameter that tells the function what to check for errors. It may be an expression, formula, or cell reference.
'value_if_error' This is also a required parameter that represents the value returned from the function in case of an error. It may be an empty string, a cell reference blank cell, a line of text, a numeric value, or another formula or expression.

Important Characteristics of the IFERROR function

  • The IFERROR function deals with all types of errors. This includes #N/A, #DIV/0, #NAME, #NUM!, #NULL, #VALUE, and #REF.
  • IFERROR function is only available in Excel 2007 or higher versions.
  • To catch errors in earlier versions of Excel (2003 and below), you'll have to use the ISERROR function combined with the IF function.
  • An empty 'value' parameter will result in an empty string ("") instead of an error.
  • An empty 'value_if_error' parameter will also be evaluated as an empty string (""), and hence, no message will be displayed if an error is found.
  • IFERROR function can be used with array formulas. If an array formula is passed as a value parameter to the IFERROR function, the function returns an array of results for every cell in the specified range.

Types of Formula Errors in Excel

Before going any further, let's see what all errors can excel formulas throw.

#N/A Error

The #N/A Error stands for Not Available Error. It is thrown when something that you were looking for is not found. Despite its obvious unpleasantness, it is a "useful" error because it tells you something important is missing in your formula.

It may also be triggered by an extra space between characters, wrong spellings, or inconclusive lookup tables. The functions most likely to give you a #N/A error include MATCH, LOOKUP, VLOOKUP, and HLOOKUP.

#DIV/0! Error

The #DIV/0 Error is another type of error often found in Excel. You will find this error every time a number is divided by zero (0) or an empty cell reference. It occurs when you attempt to perform calculations like 10/0.

In many situations, the #DIV/0 error is unavoidable. This is especially true if you prefer to assign formulas before populating your excel worksheets with data. In such situations, you probably don't want to see an error message at all, and this can be achieved by the IFERROR function.

#VALUE! Error

The #VALUE Error is encountered when you use an incorrect datatype parameter in a formula. It's Excel's way of saying there is something wrong with the way you've typed your formula. Or that it has identified a problem with the cells you're attempting to reference.

This error-type is very broad, and it may be hard to determine its exact cause.

Pro tip – While building a formula, you must make sure that the datatypes of all the values involved are compatible. Otherwise, you will encounter this error.

#REF! Error

The #REF! Error, also known as the reference error, is encountered when a reference in a formula has expired. Most commonly, this is the case when a formula points to a cell reference that does not exist (for example, you deleted a row or a column that was being referenced by a formula).

#NAME ERROR

This error commonly occurs as a result of misspelled functions. Unlike word, Excel demands you to type in the exact name of the function you are going to use.

If, for example, you wanted to type HLOOKUP but type HLOKUP instead, you'll get a #Name error. It means part of your formula needs fixing. Most times, you can avoid it by using the formula wizard.

#NUM ERROR

The #NUM Error occurs when the value you are trying to calculate is beyond Excel's limits. There are various conditions in which the #NUM Error can occur.

Another scenario that can return a #Num error is when you try to provide a non-numeric value (or any invalid value) to a function argument that only supports numeric values. For example – trying to calculate the square root of a negative number SQRT(-10) will return a #NUM error.

Examples of IFERROR Function

Now let's try to understand the IFERROR function with some examples.

Example 1 – Handling #NA errors in VLOOKUP Function

Let's assume we have a list of students along with their registration numbers. We try to find the registration number for a student with the name "Glen" out of that list.

IFERROR_EXAMPLE_01

For this, we will be writing a VLOOKUP function as –

=VLOOKUP("Glen",A1:B11,2,0)

But since 'Glen' doesn't exist in the student list, so VLOOKUP gives a #N/A error, as shown in the above image.

Now, to replace #N/A with a more meaningful text like "Student Not Found!" we'll have to use the IFERROR function.

To use the IFERROR function in this case, we will need to use the VLOOKUP function as the first parameter to the IFERROR and text "Student Not Found!" as the second parameter.

IFERROR_WITH_VLOOKUP_NA_ERROR_02

The formula should look like this:

=IFERROR(VLOOKUP("Glen",A1:B11,2,0),"Student Not Found!")

Example 2 – Handling #VALUE! Or Invalid Parameter Errors

Let's assume we have a task planner spreadsheet that calculates the days between two dates using the NETWORKDAYS function as shown.

IFERROR_WITH_VALUE_ERROR_03

As shown in the image, Task – 4 is an ongoing task and does not have an enddate, and hence the enddate is marked as 'NA'. But while calculating days, NETWORKDAYS displays a #VALUE! error.

To fix this error, we can wrap our formula inside an IFERROR function and display a suitable message instead of an error.

IFERROR_WITH_VALUE_ERROR_04

So, our formula would be:

=IFERROR(NETWORKDAYS(B6,C6), "Ongoing Task")

Example 3 – Handling #DIV/0! Or Divide By Zero Errors

Now let's suppose we have a weekly sales stats report of a gift store. The report represents total orders generated by each sales manager along with the total sales value.

IFERROR_WITH_DIVIDE_BY_ZERO_ERROR_05

In the report, we want to calculate the 'Average Order Value' generated by each sales manager. For calculating the 'average order value', we can use a formula – Total Sales Value / Total Orders.

An excel formula can represent this as:

=C3/B3

As we can see, the report sales manager 'Donald Perkins' has made zero sales for the week, and hence this formula results in an error.

The #DIV/0 error really stands out, and it would be embarrassing to have such errors appear in your excel report.

We can use the IFERROR function in such a case and pass our existing formula as the first argument to the function, and the second argument would be a suitable message or an empty string.

IFERROR_WITH_DIVIDE_BY_ZERO_ERROR_06

So, the final formula would be:

=IFERROR(C3/B3,"")

This will show an empty string in case an error occurs in the inner formula.

Example 4 – Handling Errors In Array Formulas Using IFERROR Function

Array formulas in excel allow you to perform powerful calculations on one or more value sets. Let's take example 3 and use an array formula to populate 'Average Order Value' against each row.

To do this, we will have an array formula that divides each value in the cell range C3:C7 by the appropriate cell within the range B3:B7, and then press 'control + shift + enter' keys.

The formula would be:

{=C3:C7/B3:B7}

And as we can see with the array formula, we are again getting a '# DIV/0' error for the D6 cell.

To fix this, we can use the IFERROR function. IFERROR function, when used with an array formula, returns an array of values for each cell in the range provided.

So our final formula would be:

{=IFERROR(C3:C7/B3:B7,"")}

IFERROR_WITH_ARRAY_FORMULA_08

This formula divides each value in the range C3:C7 with an appropriate value from range B3:B7, and then returns an array of results. The IFERROR function captures all the #DIV/0 errors and replaces them with empty strings.

Example 5 – Importance of IFERROR Function in Excel Calculators and Templates

Let's consider you are building a Monthly Loan Repayment Calculator in Excel, as shown below.

IFERROR_WITH_Templates_09

This template works nicely with all the data, but if the user forgets to enter any mandatory value, the formula returns an error.

IFERROR_WITH_Templates_With_Errros_10

Errors like this stand out, and it is quite awkward to have such errors in your excel templates.

IFERROR_WITH_Templates_With_IFError_11

To fix this, you could wrap your formulas in an IFERORR function and display a more meaningful message to the end-user. For example:

=IFERROR(PMT((B4%/12),B5,B3,0), "Mandatory fields should not be blank!")

IFERROR vs. ISERROR

The ISERROR function is an error-handling alternative for those who are working with Excel 2003 or lower versions. Since the IFERROR function came out with Excel 2007 so before that, for handling formula errors, we used ISERROR Function with Excel IF Statement.

ISERROR function accepts a single argument, which can be an expression, formula, or cell reference. If the supplied argument results in an error, it returns TRUE otherwise, it returns FALSE.

The errors that the ISERROR function checks are as follows:

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

ISERROR Function differs from the IFERROR in the sense that – the ISERROR function returns a Boolean value if there is an error or not. On the other hand, the IFERROR function allows the user to put up a custom and meaningful value or string instead of the error.

Syntax of ISERROR Function

The syntax of the ISERROR function is as follows:

=ISERROR(value)

'value' – This a required parameter that tells the function what to check for errors. It may be an expression, formula, or cell reference.

ISERROR Function Example

Let's again revisit Example 1 and try to handle the error using the ISERROR function. So, we have a list of students along with their registration numbers. We try to find the registration number for a student with the name "Glen" out of that list.

IFERROR_EXAMPLE_01

For this, we will be writing a VLOOKUP function as –

=VLOOKUP("Glen",A1:B11,2,0)

But since 'Glen' doesn't exist in the student list, so VLOOKUP gives a #N/A error, as shown in the above image. In the previous example, we used the IFERROR function to replace the #N/A error with a more meaningful text like "Student Not Found!"

But here, let's try to use the ISERROR function along with the IF function to achieve the same result. So, the formula would be:

ISERROR_WITH_IF_12

=IF(ISERROR(VLOOKUP("Glen",A2:B11,2,0)),"Student Not Found!", VLOOKUP("Glen",A2:B11,2,0))

NOTE: IFERROR function automatically assumes you will always want the result if your calculations don't have any errors. ISERROR, on the other hand, gives you either "TRUE" or a "FALSE", and when combined with the IF function, you can have more control over the result in both outcomes.

IFERROR vs. IFNA

IFNA Function is another error handling function in Excel. However, the IFNA function only catches #N/A errors and allows us to display more meaningful error messages in case of a #N/A error.

Syntax of IFNA Function

The syntax of IFNA Function is as follows:

=IFNA(value, value_if_na)

'value' – the expression, formula, or reference excel should be checked for errors.

'value_if_na' – represents the value returned from the function in case of an #N/A error. It may be an empty string, a cell reference blank cell, a line of text, a numeric value, or another formula or expression.

IFNA works best with lookup functions such as VLOOKUP, MATCH, HLOOKUP, and LOOKUP.

IFNA Function Example

Let's try to understand the IFNA function again with Example 1.

So, we have a list of students, and we are trying to fetch details for a student named "Glen" using a VLOOKUP function. However, since "Glen" is not a part of the list, so the VLOOKUP function throws a #N/A error.

IFNA_EXAMPLE_13

To handle such errors, we make use of the IFNA function as –

=IFNA(VLOOKUP("Glen",A2:B11,2,0),"Student Not Found!")

This formula results in a more meaningful text, "Student Not Found!" if no student with the name "Glen" is found in the given range.

Please Note: IFNA Function only handles #N/A errors and does not catch or handle any other errors, as shown in the below screenshot.

IFNA_EXAMPLE_14

In the above image, we can clearly see that the IFNA function misses the #DIV/0! Error and returns the error as it is.

Recommended ReadingISNA Function In Excel

So this was all about the IFERROR function in excel. We will get back to you with another amazing function. Stay Tuned!

About Ankit Kaul

Well, I am Ankit Kaul, the founder of Excel Trick. I am a die-hard fan of Microsoft Excel and have been working with spreadsheets for the past 10+ years. My only aim is to turn you guys into 'Excel Geeks'. Check out more about me here.