IFERROR is a function found under the Excel Logical Functions category. IFERROR belongs to a group of error-checking functions such as ISERR, ISERROR, IFERROR, and ISNA. The function will return a customized output – usually set to a string of text – whenever the evaluation of the formula is an error.
In financial analysis, we need to deal with formulas and data. Often, we may get errors in cells with a formula. IFERROR is often used for error handling/capture. It handles errors such as #N/A, #VALUE!, #REF!, #DIV/0!, #NUM! #NAME? or #NULL!.
For example, the formula in C2 is A2/B2, where:
Instead of the resulting error, we can use IFERROR to return a customized message such as “Invalid input.”
The IFERROR Function uses the following arguments:
Value (required argument) – This is the expression or value that needs to be tested. It is generally provided as a cell address.
Value_if_error (required argument) – The value that will be returned if the formula evaluates to an error.
As a worksheet function, IFERROR can be entered as part of a formula in a cell of a worksheet.
To understand the uses of the function, let’s consider a few examples:
IFERROR – Error Capturing
Suppose we are given the following data:
Using the IFERROR formula, we can remove these errors. We will put a customized message – “Invalid data.” The formula to be used is:
We will get the result below:
IFERROR + VLOOKUP Function
A very common use of IFERROR function is with the VLOOKUP function, where it is used to point out values that can’t be found.
Suppose we operate a restaurant and maintain an inventory of a few vegetables and fruits. Now we compare that inventory with the list of items we want according to the day’s menu. The results look like this:
Instead of the #N/A error message, we can input a customized message. That looks better when we are using a long list.
The formula to use will be:
We get the result below:
Suppose we are given monthly sales data for the first quarter. We create three worksheets – Jan, Feb, and March.
Now we wish to find out the sales figures from certain order ids. As data would be derived by VLOOKUP from three sheets, using IFERROR, we can use the following formula: