Software

Eliminate those unfriendly error messages with Excel 2007's IFERROR function

Excel users know about the #N/A! error message but not know what it means. Mary Ann Richardson tells how to make the messages more user friendly, explaining the reason for a particular error message instance so that users can more easily remedy it.

Do your Excel users know what to do when they get an error -- or do they just cringe when they see that dreaded #N/A!? For example, say your application lets them enter an employee ID number the Vlookup function uses to look up an employee's name. If no number in the table corresponds to the user's entry, Excel automatically returns #N/A! In response to that error message, the user may often spend quite a bit of time retrying that entry. A better message might be one that explains that the employee number is no active since the employee has left the company.

To replace the cryptic message with a meaningful message, earlier versions of Excel would require you to rewrite the lookup formula as part of an IF statement, such as:

=IF(ISNA(VLOOKUP(A2,$AA$1:$AB$99,2,False)),"This employee has left the company.", VLOOKUP(A2,$AA$1:$AB$99,2,False))

While this statement would certainly make your spreadsheet more user friendly, it is not very efficient, forcing Excel to calculate VLOOKUP twice if the employee number is in the lookup table. To avoid having to recalculate the function twice in an IF statement, Excel 2007 offers an alternative: the IFERROR() function. Using IFERROR, you would replace the above formula with the following:

=IFERROR(VLOOKUP(A2,$AA$1:$AB$99,2,False), "This employee has left the company.")

In addition to #N/A!, you can use the IFERROR() function to capture #DIV/0!, #NUM!, #NAME?, and #NULL! errors that you anticipate your users might get while working with your worksheet. For example, if a certain calculation frequently yields division by zero errors, the following IFERROR statement would provide a more meaningful message:

=IFERROR(D3/D6,"You cannot divide by zero. Change the value in D6 to a nonzero number.")

Miss an Excel tip?

Check out the Microsoft Excel archive, and catch up on other Excel tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

8 comments
love2oceanfl
love2oceanfl

I have an Excel 2007, everytime I open it, it says "Cannot run the macro ?EUROTOOL.XLAM!OnRibbonLoad?. The macro not be available in this workbook or all macros may be disabled" Anybody knows why?

RandyVG
RandyVG

That's a great add and one that previous versions would benefit greatly if they had it. I too used the nested IF(IF(...)...) approach and this makes it much easier to read and write. Does anyone know what happens when this 2007 function runs in a 2003 or earlier host?

Zahra B.
Zahra B.

Good to know about Excel 2007. I've been using the "IF(ISERROR(...)...)" formula in my own spreadsheets (in Excel 2003) to catch those divisions by zero. My complete formula looks like this : =IF(ISERROR(b8/b25), "N/A", b8/b25-1)

Avner_Uzan
Avner_Uzan

Use the "=IFERROR" Anyplace that you either expect or see a "#NA" or #DIV/0!, #NUM!, #NAME?, and #NULL! it's 100% more efficient than the old way & easier to read. Not a big win functionally maybe but esthetically!

Rafiki
Rafiki

It replaces the original formula e.g. =VLOOKUP(A2,$AA$1:$AB$99,2,False) =IFERROR(VLOOKUP(A2,$AA$1:$AB$99,2,False), ?This employee has left the company.?) Scary thing to do do as maybe there is an error at A2 and the generic answer would make you overlook it.

dhays
dhays

The person may have moved to another division and the phone # is just not assigned, if one looks it up by using the phone #, so to say no longer with the company can be completely wrong. Nice to shorten a formula, but explanations are not always comprehensive. The program doesn't give "div by 0" errors anymore? Why would they mess with soething like that? It seems like every time MS Office is "improved" it also takes away some functionality.

Editor's Picks