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.