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.
IFERROR Function in Excel (Table of Contents)
IFERROR In Excel
IFERROR function in Excel checks for any error condition. It returns a different result if the first argument of the function evaluates to an error. The word IFERROR itself denotes if an error occurs executing the statement passed as a second argument, hence the formula of IFERROR takes two arguments.
IFERROR Formula in Excel
Explanation of IFERROR Function in Excel
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
IFERROR function in Excel allows us to fix errors with a customizable output just in case a formula gives us an error. IFERROR in Excel is categorized as a Logical function in Excel. There are several categories under which a formula error could fall, an error could be syntactical, logical, semantic and reference error.
Syntax Error can occur if a formula has mismatched parenthesis, or if a function may not have the correct number of arguments. A logical error occurs when a formula syntax wise is correct but do not execute the desired output. A semantic error occurs for example when a function name that is spelled incorrectly. Excel attempts to interpret the misspelled function as a name and displays the #NAME? error. So, depending upon the nature of the error the excel returns following error values
- #N/A error occurs when a cell referenced by a formula displays #N/A, example when a lookup function like HLOOKUP, LOOKUP, MATCH or VLOOKUP cannot find a match.
- #DIV/0! occurs for an operation like division by zero. If a formula attempts to divide by zero Excel displays #DIV/0! Error value.
- #NAME? Error occurs if a formula contains an undefined range or cell name or if a formula contains text that Excel interprets as an undefined name. Does a misspelled function name generate a #NAME? error.
- #NULL! Error occurs when a formula attempts to use the intersection of two ranges that don’t actually intersect.
- #NUM! Error occurs if you pass a non-numeric argument to a function when a numeric argument is expected and when an invalid argument is passed to a function.
- #REF! Error occurs when a formula uses an invalid cell reference.
- #VALUE! Error is very common and can occur when an argument for a function is of an incorrect data type or the formula attempts to perform an operation using incorrect data. For example, a formula that adds value to a text string returns the #VALUE! Error.
How to Use IFERROR in Excel?
So, in order to handle the above-explained errors, IFERROR function in Excel is often used. Let’s take some IFERROR Excel Function examples to illustrate this in a better manner.
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 list and separate out the names that are not in list A.
Applying, 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 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 excel function to replace #N/A error,
So, we will pass vlookup formula, VLOOKUP(B2,$A:$A,1,0) as first argument and Name not in list A as the second argument to the IFERROR function in Excel. 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 IFERROR function in Excel.
=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 sum we get #NAME? error.
Instead of SUM, if the user writes the wrong formula, #NAME? an error is generated.
Using 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 ‘+’ 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 excel 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 IFERROR function in Excel.
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 inbuilt function SQRT 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 SQRT function is applied to a negative number.
Using 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 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 value we get a #VALUE! Error.
Since an incorrect data type is used in the formula, it returned a #VALUE! Error
Correcting this using IFERROR excel function, we have
So, we can see IFERROR function in Excel could be very useful in handling the errors in excel 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 IFERROR in Excel along with practical examples and downloadable excel templates. You may also look at these useful functions in excel –
- Creating Macro by writing code in Excel VBA
- How to use VLookup in Excel VBA?
- How to Display or hide Fill Handle in Excel?
- IFERROR with VLOOKUP in Excel
- Step to Record Macros in Excel
- How to use ISERROR Function?
- HLOOKUP in Excel
- LOOKUP Function in Excel
- VLOOKUP Excel Function
- INDIRECT Function in Excel
- Match Excel Function
- Choose Function Excel