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

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.