Before the introduction of the IFERROR function in Excel 2007, if you wanted to hide errors displayed by some formulas you had to employ a combination of IF and the ISNA functions, which resulted in Excel having to do the calculation twice. Once to establish whether the result was an error, and again if the result wasn't an error.
In big spreadsheets this meant significant perfomance issues. Thankfully Excel 2007 introduced the IFERROR formula which puts an end to these messy workarounds.
It can be used as a ‘wrapper’ to hide many different errors; #DIV/0!, #NAME?, #NULL!, #NUM!, #REF!, #VALUE!, and one of the most common being VLOOKUP’s #N/A error.
In this tutorial we’re going to look at using it to solve VLOOKUP's #N/A error.
We’ll cover why we might want Excel to hide this error, and how we can tell Excel to display something more elegant in its place.
Let’s recap VLOOKUP formulas:
Exact Match VLOOKUP:
VLOOKUP(find this value, in that table, return the value in column x of the table, but only return a result if you can match the value exactly)
Sorted List VLOOKUP:
VLOOKUP(find this value, in that table, return the value in column x of the table)
Excel will return a #N/A error if it can’t find the value it’s looking for in the table.
Problems with #N/A
1) In some situations you are anticipating errors that you don’t need to correct. However, a spreadsheet littered with #N/A’s can be unsightly when you’re presenting the data in a report format.
2) #N/A’s present in any cell of a row or column will prevent you adding it up. The result of a SUM on a row or column with #N/A’s will be #N/A. That’s a show stopper right there.
3) #N/A’s are not very informative. Something like ‘Not Found’ or ‘Missing’, or ‘0’ would be more helpful.
4) Just like you can’t add up a column containing cells with #N/A’s, you can’t apply any other formulas to them either. It would be more helpful for Excel to enter a 0 (zero) which won’t break any dependant formulas (well, unless of course you’re dividing by 0).
IFERROR Gets Rid of #N/A
By wrapping your VLOOKUP in an IFERROR function you can tell Excel to hide the error, or put something else (text, a number, or nothing) in its place.
Taking our VLOOKUP example above, and wrapping it in IFERROR, in English our new formula would read:
=IFERROR(VLOOKUP(find this value, in that table, return the value in column x of the table, but only return a result if you can match the value exactly), if you can’t find it put the word 'Missing' in the cell)
When we enter our formula in Excel, and apply it to the example we used for our VLOOKUP Exact Match example it would look like this:
You can see in the spreadsheet below that ‘Doug’ is no longer in our Commission Rates table and the IFERROR formula is telling Excel to put the word ‘Missing’ in the cell.
If we wanted Excel to put a number, say 0 in the cell instead of a word our formula would look like this.
You’ll notice the 0 doesn’t have double quotes " " surrounding it like the text 'Missing' did. The rule is if you want Excel to enter text you need to surround it in double quotes, but for numbers you just enter them without the double quotes.
To enter nothing, it would read:
To enter a dash – it would read:
As I mentioned above, it can also hide #DIV/0!, #NAME?, #NULL!, #NUM!, #REF!, and #VALUE!
The other most common error is #DIV/0!
Say we had a calculation that was =10/0 the result would be #DIV/0!. To hide this we can wrap our formula in the IFERROR like this:
and instead of Excel displaying #DIV/0! it would display ‘Error’
Or if we wanted it to display 0 we’d enter it like this:
One last thing - Cell Error Print Options
What if you want to keep the errors in the spreadsheet and only hide them when printing? Sometimes it's useful to know where the errors are so you can correct any that are not expected, but if you regularly print reports you probably don't want the errors displayed.
There's a simple print setting that will allow you to define how errors are displayed when printing. You can choose to either enter a -- in the place of any errors, or leave the cell blank.
In the Page Setup on the Sheet tab choose how you want cell errors displayed from the drop down list.
Enter your email address below to download the sample workbook.
Did you find this useful? Why not share the knowledge with your friends and colleagues on LinkedIn, Google+, Twitter, or Facebook.