How to Use IFERROR Function in Excel (4 Practical Examples)

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

Argument Description

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:

• 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:

• 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:

• Next, press the Enter key and then type in the below formula in cell F7:

• After that, press Ctrl+Shift+Enter to apply the array formula.

Read More: How to SUM with IFERROR in Excel

3. Applying IFERROR Function with VLOOKUP

You can combine the IFERROR function with the VLOOKUP function in Excel when looking up any value. 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:

• 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 instead of getting a 0 value whenever there is any sort of error. 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:

• 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.

Excel IFERROR Function: Knowledge Hub

• How to Use IF and IFERROR Combined in Excel
• Excel ISERROR vs IFERROR Functions
• How to Use Multiple IFERROR Statements in Excel
• How to Use Conditional Formatting with IFERROR in Excel

<< Go Back to Excel Functions | Learn Excel