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

Get FREE Advanced Excel Exercises with Solutions!

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.


Download Practice Workbook

You can download the practice workbook from here.


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.


4 Practical Examples to Use IFERROR Function in Excel

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.

Excel IFERROR Function


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

use the IFERROR function in excel for basic error

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


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)

use the IFERROR function in excel in array formula

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


Similar Readings


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")

use the IFERROR function in excel with vlookup

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

use the IFERROR function in excel vba

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


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. Lastly, to learn more excel techniques, follow our ExcelDemy website. If you have any queries, please let me know in the comments.


Related Articles

Shakil Ahmed

Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo