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.
- 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
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.
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
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.
Example #2 – Handling #DIV/0! Error
We have amounts in Column A that needs to be distributed equally amongst the number of people given.
So, when we apply formula =Amount/Number of People
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’)
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.
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.’
Example #4 -Handling #NULL! Error
Suppose we have 3 values in cell A2, A3, and A4, and we want to sum them.
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.
So, to rectify this error, we can use the IFERROR function by changing the formula to
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.
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. to calculate the square root of a number when we apply this formula to get the square root of the numbers.
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.
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.
Now, if we delete the third row, by selecting 3rd row completely, we get a #REF! error in cell C2
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.’
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.
Since an incorrect data type is used in the formula, it returned a #VALUE! Error
Correcting this using IFERROR, we have
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. and customizing the output when an error occurs as per need or requirement.
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 –