The Excel IFERROR function lets you return a custom value (a blank cell or a text value) if the given formula generates an error.
For example, suppose that your task is to send birthday greetings to each employee and organise a small celebration. You’re using the VLOOKUP function to manage and organise the data. Here’s the table (Main Table) you prepared using raw data (Source Table).
For a few values, you are getting a #N/A error, as shown in the image above. If you wish to get rid of those #N/As, you can use the following formula:
=IFERROR(VLOOKUP(E3, $B$3:$C$8, 2, 0), "")Where,
- VLOOKUP(E3, $B$3:$C$8, 2, 0) is the core formula to match values and organize data
- “” helps the IFERROR function display a blank value if the VLOOKUP formula returns a #N/A error
The results are displayed in the following image,
Syntax
The IFFERROR is a simple logical function with the following syntax.
=IFERROR(value,value_if_error)
=IFERROR(VLOOKUP(E3, $B$3:$C$8, 2, 0), "")Where,
- value argument needs to be replaced with the value, formula, cell reference, etc.
- value_if_error argument lets you specify a custom value if an error is found
Important Notes:
- The function is compatible with all types of errors
- It cannot catch errors returned due to incorrect function syntax
- If the value is empty, the function evaluates it as an empty string
How to use the IFERROR Function in Excel
Download the example spreadsheet used to demonstrate the IFERROR function in the upcoming section. Practice is the key to success!
Example 1: IFERROR Function Basics
Suppose that you wish to find the average score per test for each student. We have the following data:
We will simply divide the total score by the tests to find average scores.
Average Score = Total Score ÷ Number of Tests Given
Here is a quick GIF.
Here, we will get a #DIV/0! error if any of the student didn’t appear for any test. But if we wish to get rid of that error and display a message as “No Test Taken”, we need to use the IFFERROR function.
Here are the steps,
- Select the cell D1
- Type =IFERROR
- Choose the first option from the popup
- Next, put the formula B2/C2
(It is the formula to calculate Jack’s average score) - Type , to move to the next argument
- Specify “No Test Taken” as the output if the formula returns a #DIV/0!
- Complete the bracket using )
- Hit the Enter key
Our formula would be as follows:
=IFERROR(B2/C2, "No Taken Test")Where,
- B2/C2 is the formula to calculate the average score
- No Taken Test is the desired output if the formula returns a #DIV/0! Error
The results are displayed in the following image,
Example 2: Using IFFERROR to Add Values while Ignoring Errors in the Given Range
It is one of the creative uses of the IFERROR function in Excel.
Consider the following dataset.
Here we have product information, including their prices and quantities sold over the given period. As you can see, a few cells include #N/A error for some reason. Such errors exist especially when we export data from online sources, use formulas to perform specific calculations, or extract data from a raw file.
Now, suppose that we want to perform further calculations on the given data. We need to calculate the total quantities sold.
We will simply use the SUM function in Excel. But after doing so, Excel returned a #N/A error as shown in the following image.
The entire calculation is broken due to the existing #N/A errors in the table. You may be wondering if there is any way to bypass or ignore these #N/A entries and find the sum of the remaining entries from the given range.
Yes, of course. You can do it using the IFERROR function.
Here are the steps,
- Select the desired cell
- Type =SUM
- Choose the first option from the popup
- Type IFERROR
(As IFERROR is a built-in function in Excel, you will see the following options) - Double-click the IFERROR command from the list
- Specify the C2:C11 cell range to add values
- Type , to move to the next argument of the IFERROR function
- Specify 0 as the default value if there are any errors in the given range
- Complete the bracket for the IFERROR function using )
- Similarly, complete the bracket for the SUM function using )
- Press the Enter key
Our final formula would be as follows,
=SUM(IFERROR(C2:C11,0))Where,
- C2:C11 is the range that includes values to be added
- IFERROR(C2:C11,0) is the formula to ignore errors in the range while adding values
The results are displayed in the following image,
When to use? – IFERROR vs IFNA
The limitation of the IFERROR function lies in its strength. The function catches all types of errors in Excel. For example, suppose by mistake I misspelled a function, the Excel returns a #NAME? error. The IFERROR catches this error, too.
The IFNA function is mostly used in the case of the VLOOKUP formulas. It is a better alternative to the IFERROR function when identifying the #N/A errors returned by the VLOOKUP formula.
The IFNA function has a similar syntax:
=IFNA(value, value_if_na)Where,
- value argument needs to be replaced with the VLOOKUP formula
- value_if_error is used to specify a custom value if an error is found
Takeaway
The IFERROR is one of the most used functions in Excel. It is used to prepare clean and easy-to-understand tables, reports, dashboards, etc.
It has a simple syntax. Anyone can learn it without any prior spreadsheet experience. I hope this article taught you all the bells and whistles of the IFFERROR function.
Please comment below if you are stuck or encounter any particular error while using the IFERROR function in Excel. I will answer your questions as soon as possible.
Get an Office 365 Subscription to Access all the powerful Functions and Tools in Excel.