How to Use IFERROR in Excel Formula? Microsoft Excel is a versatile tool with a wide range of functions and formulas, but it’s not uncommon to encounter errors when working with complex formulas or large datasets. The IFERROR function in Excel is a valuable tool that allows users to identify and handle errors effectively. In this article, we will explore the IFERROR function, what errors it can identify, and how to use it in four different ways.
What is the IFERROR Function in Excel?
The IFERROR function is a built-in Excel function that helps handle errors within formulas. It allows you to specify a value or an action to be taken if a formula generates an error. By using IFERROR, you can replace error values with alternative outputs or leave them blank. This is the typical syntax for the IFERROR function:
=IFERROR(value, value_if_error)
What are the Errors Identified by the IFERROR Function?
The IFERROR function is designed to recognize various types of errors in Excel. Here are some common errors that the IFERROR function can identify:
Download File
a) #DIV/0!: This error occurs when a formula has to divide a number by zero.
b) #N/A: This error appears when a value is unavailable or cannot be found.
c) #VALUE!: This error occurs when a formula comprises an incorrect data type or invalid reference.
d) #REF!: This error indicates an invalid cell reference in a formula.
e) #NAME?: This error appears when Excel cannot recognize a text as a valid name or reference.
f) #NUM!: This error occurs when a numeric value is invalid or exceeds Excel’s calculation limits.
g) #NULL!: This error appears when a formula has an incorrect intersection of two ranges.
Methods to Use IFERROR in Excel
Use IFERROR to Eliminate Basic Error
One of the simplest ways to use the IFERROR function is to eliminate basic errors such as #DIV/0!. This error occurs when you divide a number by zero. To handle this error, you can apply the IFERROR function to replace it with a more meaningful output or a blank cell.
For example, if you have a division formula in cell C1 and want to replace any #DIV/0! For errors with a zero, you can use the following formula in cell D1:
=IFERROR(A2, 0)
This formula will display the result of the division in cell A2. If an error occurs, it will replace it with a zero.
Use IFERROR Function With Array Formula
The IFERROR function can also be helpful with array formulas, which enable you to simultaneously perform calculations on many cells or ranges. When using IFERROR with an array formula, the function will evaluate each array element and apply the desired action to any errors encountered.
For example, if you have a range of numbers in A1:A5 and you want to calculate their reciprocals, you can use the following array formula in B1:B5:
{=IFERROR(1/A1:A5, “Error”)}
Remember to enter the formula using Ctrl + Shift + Enter to apply it as an array formula. This formula will calculate the reciprocal of each number in the range A1:A5. If an error occurs, it will show the text “Error” instead.
How to Use IFERROR With Vlookup
The IFERROR function can be beneficial when working with the VLOOKUP function. VLOOKUP is commonly used to search for a value in a table and retrieve a corresponding value from another column. However, VLOOKUP returns an #N/A error if the value is not found. By using IFERROR, you can handle this error and display a custom message or alternative value.
For example, if you have a VLOOKUP formula in cell C1 to search for a value in column A and retrieve the corresponding value from column B, you can use the following formula in cell D1:
See More: VBA Error Handling
=IFERROR(VLOOKUP(A1, B:C, 2, FALSE), ” Value Not found”)
This formula will perform the VLOOKUP operation. If the value is found, it will return the corresponding value from column B. If the value is not found, it will display the text “Value not found” instead of the #N/A error.
Use IFERROR in VBA
In addition to using IFERROR within Excel formulas, utilize it in Visual Basic for Applications (VBA) code. It allows you to handle errors programmatically and provide alternative actions or outputs.
For example, consider the following VBA code that divides two numbers and handles any errors using IFERROR:
| Sub DivideNumber()
On Error Resume Next Dim dResult As Double dResult = 10 / 0 MsgBox “Result: ” & Application.WorksheetFunction.IfError(dResult , “Error”) End Sub |
The division operation (10 / 0) in this code would generally cause an error. However, by using IFERROR, the error is handled, and a message box displays the result. The message box will show “Error” if an error occurs.
Conclusion
The IFERROR function in Excel offers versatility in handling various types of errors. Using it differently can eliminate basic errors, work with array formulas, handle errors in VLOOKUP operations, and incorporate them into VBA code. These techniques enable you to create more robust spreadsheets, enhance data analysis, and streamline workflows.
