Excel Functions Tutorials
- Excel Formulas Cheatsheet
- Financial Functions in Excel
- Logical Functions in Excel
- TEXT Functions in Excel
- Lookup Reference in Excel
- Address Function in Excel
- Choose Function in Excel
- Column Function in Excel
- Columns Function in Excel
- REPLACE Function in Excel
- GetPivotData in Excel
- HLOOKUP in Excel
- Hyperlink Excel Function
- INDIRECT Function in Excel
- LOOKUP Excel Function
- Match Excel Function
- VLOOKUP Excel Function
- INDEX Excel Function
- VLOOKUP vs HLOOKUP
- Maths Functions in Excel
- POWER Function in Excel
- EVEN Function in Excel
- ODD Function in Excel
- ABS Function in Excel
- SUM Function in Excel
- SUMPRODUCT Function in Excel
- SUBTOTAL Excel Function
- ROUND in Excel
- AGGREGATE Excel Function
- PRODUCT Excel Function
- RAND Excel Function
- LOG Excel Function
- EXPONENTIAL Excel Function
- SUMIF in Excel
- TAN Excel Function
- CEILING Excel Function
- LN Excel Function
- SIGN Excel Function
- COS Excel Function
- FLOOR Function in Excel
- SIN Excel Function
- Date and Time Function in Excel
- Statistical Function in Excel
IFERROR in Excel (Table of Contents)
IFERROR In Excel
IFERROR function in Excel returns a value which is specified, whenever a formula gives an error. There are situations where a formula often generates an error, in that case, if we want to get a customized output or we want to avoid that error been generated we can use IFERROR function in excel efficiently. If we prefer to get an output something other than the error, we can use the IFERROR function in Excel. This function was introduced in Excel 2007.
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 error occurs execute 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. A misspelled function name generates 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 a 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. Lets take some IFERROR Excel Function examples to illustrate this in a better manner.
Example #1 – Handling #N/A Error Using IFERROR in Excel
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 Using IFERROR in Excel
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 Using IFERROR in Excel
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 wrong formula, #NAME? 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 Using IFERROR in Excel
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 a 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 Using IFERROR in Excel
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 Using IFERROR in Excel
Suppose, we have two numbers in cell A2 and A3 and we want to divide 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 IFERROR excel function, we can pass a value_if_error, ‘Reference Deleted’.
Example #7 – Handling #VALUE! Error Using IFERROR in Excel
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.
You can download this IFERROR in Excel template here – IFERROR Function Excel Template
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 –