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.

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.

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.

Handling #NA errors in VLOOKUP Function

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.

replace #N/A with a more meaningful text like "Student Not Found!"

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.

Handling #VALUE! Or Invalid Parameter Errors

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.

display a suitable message instead of an error

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.

Handling #DIV/0! Or Divide By Zero Errors

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.

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

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.

Handling Errors In Array Formulas Using IFERROR Function

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 function, when used with an array formula, returns an array of values for each cell in the range provided

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.

Importance of IFERROR Function in Excel Calculators and Templates

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.

wrap your formulas in an IFERORR function and display a more meaningful message to the end-user

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.

ISERROR Function Example

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 function along with the IF function to achieve the same result
=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.

Recommended Reading: Excel ISERR Function – How to Use

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.

IFERROR vs. IFNA

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 Function only handles #N/A errors and does not catch or handle any other errors

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 Supriya

Supriya, ExcelTrick's Editor-in-Chief, combines her MBA in Human Resources with vast Excel proficiency for data-driven decisions. Her prior role in Corporate HR solidified Excel as her trusted companion. In her leisure, she cherishes family time, gaming, and reading. Get to know Supriya better here.