IFERROR Excel Function

What is the Excel IFERROR Function used for?

IFERROR excel function is used for error handling in excel, this function is used to display a result when error occurs in any formula or argument, we use this function with other function to notify the user what could be mistaken in the formula, this function takes two arguments which is valuable if there is no error and another value to be displayed when there is an error.

Syntax

IFERROR Function Formula
  • value could be any value, a reference, or even a formula
  • value_if_error is the value that is returned as an output when an error is generated

Examples to Handle Errors in Excel using IFERROR Function

You can download this IFERROR Function Excel Template here – IFERROR Function Excel Template

Example #1 – Handling #N/A Error

Suppose we have two lists of Employees, in Column A and B, in column B, there are some employees that are not in column A, and we want to find out the names of those employees that are not in list A.

IFERROR Function Example

So, we applied the VLOOKUP formula to the right adjacent column of list B to find out the common names in both lists and separate out the names that are not in list A.

VLOOKUP formula in C2 and dragging it down, we have

=VLOOKUP(B2,$A:$A,1,0)

IFERROR Function Example 1

As you can see above, there are some names that were not able to be looked up by the VLOOKUP formula; hence they generated #N/A error, which means VLOOKUP was not able to found those names in a list of the employee in column A.

Now, if we want #N/A to be replaced by some other value like, ‘Name not in list A,’ so, we will use the IFERROR function to replace #N/A error,

So, we will pass the lookup formula, VLOOKUP(B2,$A:$A,1,0) as the first argument and Name not in list A as the second argument to the IFERROR function. So, the formula becomes

=IFERROR(VLOOKUP(B2,$A:$A,1,0),”Name not in list A”)

Dragging this formula to the last cell, we get the replaced value of #N/A with the value passed.

IFERROR Function Example 2
IFERROR Function Example 3

Example #2 – Handling #DIV/0! Error

We have amounts in Column A that needs to be distributed equally amongst the number of people given.

IFERROR Function Example 4

So, when we apply formula =Amount/Number of People

IFERROR Function Example 5

We get an error #DIV/0! in C4 when the number of people is 0. When we divide a numeric value by zero, we get this error.

Now, we want to replace this error by a value ‘No of Person < 1’, we will use the IFERROR function.

=IFERROR((A2/B2)), No of Person < 1’)

IFERROR Function Example 6

Example #3 -Handling #NAME? Error

Suppose we have some numbers that need to be added, and by mistake, if someone writes a wrong formula for the sum, we get #NAME? error.

IFERROR Function Example 7
IFERROR Function Example 8

Instead of SUM, if the user writes the wrong formula, #NAME? an error is generated.

Using the IFERROR excel function, we can handle this error by simply passing the second argument a value, for example, ‘Typed Wrong Formula.’

IFERROR Function Example 9
IFERROR Function Example 10

Example #4 -Handling #NULL! Error

Suppose we have 3 values in cell A2, A3, and A4, and we want to sum them.

Handling #NULL! Error Example 4

In cell A5, we used the plus operator to add them, A2+A3 A4, and instead of placing the ‘+’ operator between A3 and A4, we entered a separator by pressing space, we get #NULL! Error.

Handling #NULL! Error Example 4-1

So, to rectify this error, we can use the IFERROR function by changing the formula to

=IFERROR((A2+A3 A4),(SUM(A2:A4)))

Handling #NULL! Error Example 4-2

You can see we have used another formula, SUM, to calculate the sum of three values given and passed it as a second argument in the IFERROR function.

Example #5 -Handling #NUM Error

We have some numbers given in Column A, and we want to find the square roots of these numbers.

Handling #NUM Error Example 5

In Excel, we have SQRT in excelSQRT In ExcelThe Square Root function is an arithmetic function built into Excel that is used to determine the square root of a given number. To use this function, type the term =SQRT and hit the tab key, which will bring up the SQRT function. Moreover, this function accepts a single argument.read more to calculate the square root of a number when we apply this formula to get the square root of the numbers.

IFERROR Function Example 5-1

We get error #NUM! for negative values, since we cannot find the square root of a negative number. So, excel throws #NUM! error when the SQRT function is applied to a negative number.

Using the IFERROR excel function, we can pass a value ‘A Negative Number’ for negative values to avoid this error.

IFERROR Function Example 16

Example #6 – Handling #REF! Error

Suppose we have two numbers in cell A2 and A3, and we want to divide the value of cell A by value cell B.

Handling #REF! Error Example 6

Now, if we delete the third row, by selecting 3rd row completely, we get a #REF! error in cell C2

Handling #REF! Error Example 6-1

Since the reference was deleted, Excel is unable to find the reference value in the formula; hence it throws #REF! error.

Using the IFERROR excel function, we can pass a value_if_error, ‘Reference Deleted.’

Handling #REF! Error Example 6-2

Example #7 – Handling #VALUE! Error

We have two values in cell A2 and A3, A2 and we want to add these two values in cell A2 and A3,

A2 contains a numeric value, whereas A3 contains a text value. When we add these two values, we get a #VALUE! Error.

Handling #VALUE! Error Example 7

Since an incorrect data type is used in the formula, it returned a #VALUE! Error

Correcting this using IFERROR, we have

Handling #VALUE! Error Example 7-1

So, we can see the IFERROR function could be very useful in errors in excelErrors In ExcelErrors in excel are common and often occur at times of applying formulas. The list of nine most common excel errors are - #DIV/0, #N/A, #NAME?, #NULL!, #NUM!, #REF!, #VALUE!, #####, Circular Reference.read more and customizing the output when an error occurs as per need or requirement.

Recommended Articles

This has been a guide to IFERROR Excel Function. Here we discuss the IFERROR Formula and how to use it along with practical examples and downloadable excel templates. You may also look at these useful functions in excel –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>