No matter how great you are with spreadsheets, sooner or later (most likely sooner) you’re going to run into an error. This happens all the time, so it’s no surprise that Excel has a function specifically to handle errors. The IFERROR function prevents error messages from cluttering your spreadsheet, and even lets you customize what you want to happen when an error occurs. And of course, when there’s no error, the IFERROR function lets your spreadsheet work like normal – it just steps in to handle any errors that pop up!
Syntax
- value is the item that’s being checked for an error
- value_if_error is what the function returns when there is an error. This can be a text string, number, blank cell, or even another formula
How to use the IFERROR Excel function
Custom error message
To see the IFERROR Excel function in action, take a look at the example below. I have some running times and distances for a group of athletes, and I’ve written a formula to show their average speed.
Unfortunately, the data has some errors that translate into error messages in column D.
There’s no question that this looks messy. Obviously the best thing to do is to fix the errors in the data, but sometimes that’s not so easy, especially with large and complex datasets.
So instead, I’ll use the IFERROR function.
Now instead of an Excel-generated error message, I get a text string that tells me there’s an error. This is achieved with the formula =IFERROR(B2/(C2/60),”Error”).
All I’ve done is added the IFERROR function with the formula I was already using as the value parameter, and the text string “Error” as the value_if_error.
Turn errors into blanks
What if you don’t want any error message at all? You can make the value_if_error a blank cell by typing “” to create an empty string.
Now instead of error messages, there’s nothing at all in those cells. Much cleaner!
IFERROR with VLOOKUP
One of the most common uses of IFERROR is in combination with the VLOOKUP function. When you use VLOOKUP to search for an exact value, you’ll get a #N/A error if the function doesn’t find a match. You can replace the error with a customized message to make your spreadsheet more comprehensive and professional!
In this example, I’m using VLOOKUP to find the sales of an employee. I search the spreadsheet for Gary, but that name isn’t in the data, so I get an error.
Now I’ll add the IFERROR function to the VLOOKUP function with the formula =IFERROR(VLOOKUP(E2,A2:C11,3,FALSE),”Employee not found”).
Now instead of a #N/A error, I get an error message telling me that the employee name wasn’t found.
And in case you’re wondering, if the employee name is there (so there’s no error in the VLOOKUP) the function works normally!
The IFERROR Excel function is very simple, but it works wonders by making your spreadsheet free of unwelcome error messages!
Tips when using IFERROR in Excel
- If you’re adding it to an existing function, just put IFERROR( before the existing function, and then ,value_if_error) after it
- The IFERROR function handles all types of errors in Excel
- If the cell that you’re evaluating is blank, it does not count as an error
- IFERROR also works for array functions
