In this tutorial, I am going to show you 4 practical examples of how to use the IFERROR function in Excel. You can use these methods even in large datasets to get any specific value that you want instead of an error. Throughout this tutorial, you will also learn some important Excel tools and techniques which will be very useful in any Excel-related task.
Overview of Excel IFERROR Function
Description
The IFERROR function in Excel can generate a custom value as a result if there is any error from the formula. If there is no error it will return the regular result.
Generic Syntax
=IFERROR(value, value_if_error)Argument Description
| ARGUMENT | REQUIREMENT | EXPLANATION |
|---|---|---|
| value | Required | The value, reference, or formula to check for an error. |
| value_if_error | Required | The set of independent data points. |
Returns
The specific value that we give as the input for an error condition.
Available In
Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, and Excel 2003.
How to Use IFERROR Function in Excel: 4 Practical Examples
We have taken a concise dataset to explain the steps clearly. The dataset has approximately 7 rows and 5 columns. Initially, we are keeping all the cells in General format and the cost column in Accounting. For all the datasets, we have 5 unique columns which are Product, Quantity, Total Cost, Ratio, and Ratio (using IFERROR). Although we may vary the number of columns later on if that is needed.
1. Eliminating Basic Error
One of the most common uses of the IFERROR function in Excel is whenever there is the possibility of getting an error. Let us see one simple example of this.
Steps:
- First, go to cell E5 and insert the following formula:
=D5/C5
- Now, press Enter and copy this formula to the cells below using Fill Handle.
- Here, you can see that there are some #DIV/0! Errors that we can eliminate using IFERROR.
- For this, go to cell F5 and insert the following formula:
=IFERROR(E5,0)
- Finally, press Enter and copy this formula down to other cells which should now give 0 instead of the errors.
2. Using IFERROR with Array Formula
We can also use the IFFERROR function with an array formula in Excel to catch any error. Follow the steps below to do this.
Steps:
- To begin with, double-click on cell F5 and enter the below formula:
=SUM(C5:C10/D5:D10)
- Next, press the Enter key and then type in the below formula in cell F7:
=SUM(IFERROR(C5:C10/D5:D10,0))
- After that, press Ctrl+Shift+Enter to apply the array formula.
3. Applying IFERROR Function with VLOOKUP
The IFERROR function in excel is very useful when looking up any value using the VLOOKUP function. It is very common to get an error if the value we are looking for doesn’t exist. Let us see how to do this.
Steps:
- To begin this method, double-click on cell F7 and insert the formula below:
=IFERROR(VLOOKUP(F5,B4:D10,3,0),"ID Doesn't Exist")
- Next press Enter and this should give a message instead of an error value.
4. Using IFERROR Function In VBA
If you are familiar with VBA in Excel, then you can write just a few lines of code and apply the IFERROR function in any cell. Below are the detailed steps to achieve this.
Steps:
- For this method, go to the Developer tab and select Visual Basic.
- Now, select Insert in the VBA window and click on Module.
- Next, type in the formula below in the new window:
Sub IFERROR()
Range("F7").Formula = "=IFERROR(VLOOKUP(F5,B4:D10,3,0),0)"
End Sub
- Then, open the macro from the Developer tab by clicking on Macros.
- Now, in the Macro window, select the IFERROR macro and click Run.
- As a result, the VBA code will give a value of 0 which means the id is not present instead of an error message.
How to Use Excel IFERROR Function to Return Blank Instead of 0
In many cases, you might want to get a blank cell value whenever there is any sort of error instead of getting a 0 value. You can do this very easily using the IFERROR function in excel.
Steps:
- To begin with, the process, navigate to cell F7 and type in the formula below:
=IFERROR(VLOOKUP(F5,B4:D10,3,0),"")
- Then, press Enter and this will give you a blank cell value instead of any type of error message.
Download Practice Workbook
You can download the practice workbook from here.
Conclusion
I hope that you were able to apply the methods that I showed in this tutorial on 4 practical examples of how to use the IFERROR function in excel. As you can see, there are quite a few ways to achieve this. So wisely choose the method that suits your situation best. If you get stuck in any of the steps, I recommend going through them a few times to clear up any confusion.
