Discussion on:

Troubleshoot VLOOKUP() formulas gotchas

hi

I've pretty much stopped using vlookup over the last few years and now use Match and Index instead. You get more control over the matching process and it's more versatile.

They're a good mix for more complex needs.

As I wrote last week, I have some bad juju in Microsoft. I get erroneous answers that are not errors sometimes if I do not sort the lookup table by the lookup value, even though I have used a "false" in the match column since I learned the function (12 to 15 years ago).

Also, I use the iserr and some of the other information functions to return information to the list of values created by the lookup. So, if I have a numeric value and add an if statement that returns an error or na to the column, I can insert a zero and use that for sudden death calculations. I.e., if a certain characteristic is not present for that row/field combination, the formula inserts the 0; I use it in a calculation and the values for that row do not go forward included in calculations for the decision or calculation operation I am performing.

If you are looking values from a different sheet, the name of the sheet should be changed since the returns from "Sheet X" can give different results as well.

Since TRUE as the match argument is not only intended to find the closest approximate value, but the closest approximate value that is less than the lookup value, the lookup column must not only be sorted, but sorted in ascending order. Will sorting the lookup column in descending order find the closest match that is higher than the lookup value? Don't know. Haven't tried it, but I'll put it on my to-do list.