The IFERROR function in Excel is your go-to tool for managing those pesky errors that can pop up in your formulas. It’s designed to catch errors and let you replace them with something more useful, like a zero, a blank cell, or even a custom message. This function is a lifesaver for cleaning up your spreadsheets and making your formulas easier to understand.
Excel throws errors when it finds a problem with a formula or the cells it’s looking at. Sometimes these errors are unavoidable, and they can make your spreadsheet look messy. If another formula references a cell with an error, it can lead to even more errors down the line. Instead of just displaying error codes, IFERROR lets you specify an alternative output.
The IFERROR function is a straightforward way to deal with errors, keeping your spreadsheets clean and your formulas clear. It has been available since Excel 2007. Let’s explore what the IFERROR function is and how you can use it to control errors in Excel.
What is the IFERROR function?
The IFERROR function is a built-in Excel function that returns a value you specify if the formula it contains results in an error.
The syntax is simple:
=IFERROR(value, value_if_error)
It only has two arguments:
- value (required) – This is the formula, expression, or cell you want to check for errors. It’s what Excel evaluates first.
- value_if_error (required) – This is what the IFERROR function will return if the first argument, “value”, results in an error. It can be a text, a number, a blank cell, or even another calculation. If it’s text, it must be within double quotes (" ").
Also, if there are no errors, IFERROR will simply return the result of the first value argument.
You can use IFERROR to manage common formula errors such as:
- #N/A
- #VALUE!
- #REF!
- #DIV/0!
- #NUM!
- #NAME?
- #NULL!
Most of the time, you can fix errors by tweaking your formulas. But sometimes errors are unavoidable. That’s where IFERROR comes in, allowing you to substitute an alternative when an error occurs.
Don’t confuse IFERROR with ISERROR. The ISERROR function only checks for errors and returns TRUE or FALSE. IFERROR actually replaces the error.
How to Use the IFERROR Function
Let’s look at how to use IFERROR to catch errors and return different types of values instead of errors.
If Error, Then Return 0
If you run into an error, you can use IFERROR to display ‘0’ instead of the error.
For example, let’s say you’re trying to calculate the average sales of different items by dividing the sales in column A by the quantity in column B.
In this case, B2 has a quantity of 0, and A5 has a #N/A error. This gives us a #DIV/0! error in C2 because you can’t divide by zero, and another #N/A error in C5 because dividing #N/A with a number also results in #N/A error.
Using IFERROR, we can display ‘0’ instead of these error codes.
To do that, nest your original formula inside IFERROR:
=IFERROR(A2/B2,0)
In the example below, we used the IFERROR formula in a separate column. This formula is entered in cell D2 and copied down the column. As you can see, the formula returns ‘0’ where an error is detected.
Also, if you skip the second argument in the IFERROR function, it defaults to ‘0’.
=IFERROR(A2/B2,)
If Error, Then Return Blank
Instead of displaying ‘0’ for errors, you can make the cell blank by using an empty string (“”) as the second argument.
To return a blank cell if there is an error, use the following formula:
=IFERROR(A2/B2,"")
The double quotes (“”) signify an empty string, resulting in a blank cell.
You can also use any character or symbol to replace an error.
To use a dash (-) instead of a blank, use this formula:
=IFERROR(A2/B2,"-")
If Error, Then Show a Message/Text String
You can display a custom message using the IFERROR function. Make sure to enclose the message in double quotes.
This formula will return the message “Invalid Input” instead of the error:
=IFERROR(A2/B2,"Invalid Input")
If Error, Then Perform a Calculation
IFERROR can perform a second calculation if the first formula produces an error. Simply input the second formula in the value_if_error argument.
Let’s say you want to find sales for Stock 1 by multiplying price with Stock 1 quantity. However, for two items there are no stocks in Stock 1, and we are getting #VALUE error because of multiplying price with a dash (-).
If the stock 1 calculation results in error, you can calculate the sales using stock 2 quantity.
=IFERROR(B2*C2,B2*D2)
If the first formula has an error, IFERROR will then do the second calculation. If not, it will return the result of the first one.
IFERROR with Array Formulas
Array formulas let you perform multiple calculations using a single formula. However, these formulas can sometimes cause errors.
For example, if you want to find the total average sales by dividing the sales in column A by quantity in column B for each row and then summing up the results. An array formula like this will result in an error:
=SUM(A2:A6/B2:B6,0)
This formula divides each cell in A2:A6 by its corresponding cell in B2:B6 and sums up the result. If any of the divisions produces an error, it will result in an error.
The division of A2 by 0 and A5 by B5 results in an error. Adding error with other values results in #DIV/0! error.
To fix this, use IFERROR to handle the errors within the array calculation:
=SUM(IFERROR(A2:A6/B2:B6,0))
Press Ctrl+Shift+Enter to execute this array formula.
The IFERROR function will replace all errors with 0 and the SUM function will add all the results to give the total average sales.
IFERROR with VLOOKUP
The IFERROR function is often used with VLOOKUP to return a custom message when a lookup value isn’t found.
VLOOKUP searches for a value in the first column of a table and returns a corresponding value from a specified column. If the lookup value isn’t found, you’ll get an #N/A! error. IFERROR can replace this error with your message.
For example, you want to find a student’s score from a table of student scores.
To look for the score of a student named ‘Roger’ and return his Biology score, use the below formula.
=VLOOKUP(J5,A2:G15,5,FALSE)
Since ‘Roger’ isn’t in the table, this gives an error. J5 is the lookup value, A2:G15 is the table, 5 is the column number for Biology and FALSE for exact match.
Using IFERROR, you can return a message like “Not found”.
=IFERROR(VLOOKUP(J5,A2:G15,5,FALSE),"Not Found")
IFERROR will catch the error and output “Not Found” instead.
If the nested VLOOKUP doesn’t give an error, IFERROR will just return the usual VLOOKUP result.
For instance, if you change the lookup value to ‘Robert’ instead of ‘Roger’ you will get the score ‘86’ for Biology.
IFERROR Function to Perform Sequential Vlookups
IFERROR can perform multiple VLOOKUP operations sequentially. It allows you to perform a second VLOOKUP if the first one results in an error.
Let’s say you have student scores in various subjects for Class 1 and Class 2. You want to find a student’s score from class 1 using VLOOKUP.
However, if the student isn’t in Class 1, you get an error. Nesting two VLOOKUPs within IFERROR will perform the second lookup if the first lookup is not successful.
=IFERROR(VLOOKUP(H17,A3:G12,3,FALSE),VLOOKUP(H17,I3:O13,3,FALSE))
The first VLOOKUP searches in A3:G12 (Class 1), and if it doesn’t find a match, the second VLOOKUP searches in I3:O13 (Class 2).
Nested IFERROR functions for Sequential Vlookups
What if the second VLOOKUP also fails? To avoid an error, create a nested IFERROR formula to return a custom message when the value isn’t found in either list.
=IFERROR(VLOOKUP(H17,A3:G12,3,FALSE),IFERROR(VLOOKUP(H17,I3:O13,3,FALSE),"Enter a valid name"))
The first VLOOKUP will search in A3:G12. If it fails, the second IFERROR will run its nested VLOOKUP in I3:O13. If the second one also fails, the message “Enter a valid name” is displayed.
IFERROR with INDEX MATCH
INDEX and MATCH is more powerful lookup function than VLOOKUP. It can be used to search for a value in a range and return a value from a specific row and column.
Just like VLOOKUP, if INDEX MATCH can’t find a value, it will throw an error. You can use IFERROR to return a custom message or perform another lookup by nesting the INDEX MATCH.
For example:
=IFERROR(INDEX(A2:G11,MATCH(D14,A2:A11,0),4),"Not available")
The MATCH function tries to find the position of the lookup value in the range A2:A11. If the value is not found, IFERROR will return “Not available”.
IF And IFERROR Function
You can combine the IFERROR function with the IF function to handle errors in more complex formulas.
For example, you want to use IF to check if student Margaret is pass or fail. But since Margaret’s score is not in the table, the formula will result in #N/A error.
To fix this, you can use the following formula:
=IFERROR(IF(B5>50,"Pass","Fail"),"Score not available")
The IF function checks if B5 is greater than 50. If it throws an error, the IFERROR function catches it and returns “Score not available”.
IFERROR vs. ISERROR
The ISERROR function checks if a formula evaluates to an error and returns TRUE or FALSE. The IFERROR function, on the other hand, returns a value you specify if there is an error.
The syntax for ISERROR is:
=ISERROR(value)
ISERROR is usually used with IF to perform a specific action if there is an error and another action if there isn’t.
For example, to get Janet’s marks in Biology, use the following:
=IF(ISERROR(VLOOKUP(H5,A2:G15,5,FALSE)),"Student not found",VLOOKUP(H5,A2:G15,5,FALSE))
ISERROR checks if the VLOOKUP results in an error. If TRUE, the IF function returns “Student not found”. If FALSE, it runs the VLOOKUP again to return the score.
If the lookup value is not found:
Or, if the student is found:
IFNA vs. IFERROR
IFNA is similar to IFERROR but only catches #N/A errors, whereas IFERROR catches all types of errors.
Syntax:
IFNA(value, value_if_na)
- value – The expression, formula, or cell you want to check for errors.
- value_if_na – What to return when there’s an #N/A error.
Let’s say you want to pull the sales amount of a few items from a table:
=VLOOKUP(F2,$A$2:$D$7,4,FALSE)
When we apply this formula to all cells, we get a #VALUE! and a #N/A error.
To handle these errors, you can use IFERROR:
=IFERROR(VLOOKUP(F2,$A$2:$D$7,4,FALSE),"Not Found")
This will handle all errors and show “Not Found” for every error in the formula.
But what if you only want to display “Not found” when there’s a #N/A error. In that case use IFNA.
=IFNA(VLOOKUP(F2,$A$2:$D$7,4,FALSE),"Not Found")
This will only catch the #N/A error and return the text “Not Found”. It will not catch the #VALUE! error.
