Software

Avoid nesting VLOOKUP() functions to mask error values

Avoid potential problems inherent with masking VLOOKUP() error values by using help cells or the new ISERROR() function.

Excel's VLOOKUP() function finds items in a list. The rules are simple, but you should be aware of a few inherent problems. We discussed most of them in Troubleshoot VLOOKUP() formula gotchas. Besides being aware of the potential problems, you'll also have to decide how to handle error values. It's common to mask error values, but in this case, error values are an important clue to problems.

The sheet below shows a typical error. The lookup value in A1 doesn't exist in the lookup column (A). The only difference between the functions in C2:C4 and those in D2:D4 is the use of the optional match argument. When using TRUE, the default (column C), you must sort the lookup table by the lookup column. Using FALSE (column D) forces Excel to search the entire lookup column, so sorting isn't necessary. When supplying an invalid lookup value, as I've done, the TRUE version returns erroneous data - this is one of the reasons it's so important to work through this function carefully. It would be easy to use the resulting values without knowing they were wrong! In contrast, the FALSE version is working for us, even though it returns an error value: we know there's a problem.

The tendency is to mask the error value. For example, you might combine the IF() and ISERROR() functions to mask the error as follows:

=IF(ISERROR(VLOOKUP($A$2,$A$7:$F$51,3,FALSE)),"Problem HERE",VLOOKUP($A$2,$A$7:$F$51,3,FALSE))

This formula returns the string "Problem HERE" when the lookup function returns an error. It's not much better than letting the function return #N/A, but the point is to show you how to control the error. How you handle it will be specific to your needs. There's a downside to this solution: it forces Excel to evaluate the lookup function twice. If the sheet contains only a few lookup functions, that's no big deal. If there are hundreds of them, your sheet's going to take a performance hit.

There are two alternatives. If you're using Excel 2003 or earlier, use a helper formula:

=IF(ISERROR(D2), "PROBLEM HERE", D2)

The solution's the same but it uses two cells instead of one. The formula in Column F checks the lookup function in D for an error. This is the formula you'd reference in subsequent calculations. The two-cell solution evaluates only one lookup function for each formula, instead of two. This breakout solution will perform much better because you've reduced the number of lookup functions by half!

If you're using Excel 2007 or 2010, you can take advantage of the new IFERROR() function:

=IFERROR(VLOOKUP($A$2,$A$7:$F$51,3,FALSE),"PROBLEM HERE!")

This function combines the IF() and ISERROR() functions, eliminating the IF() function's third argument.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

4 comments
clare.smith
clare.smith

I too use VLOOKUP an awful lot and hated the pesky #N/A. Now I can use the IFERROR and make data problems really stand out for my users!

ArchGov
ArchGov

This is great! Like Scott, I also utilize nested VLOOKUP formulas to handle exceptions. This will reduce development time and calculation times. I was totally unaware of the IFERROR function. Thanks!

Scott Lowe
Scott Lowe

I use vlookup all the time and work around the #N/A issue using the nested vlookup process as you described above. I didn't know about the existence of iferror in Excel 2010. Thanks for the tip! Scott Lowe

ssharkins
ssharkins

It's so easy to use -- you'll like it!

Editor's Picks