(Part 1 of 5. See Part 2.)
Excel offers two primary lookup methods: VLOOKUP and INDEX-MATCH. Although the two methods are similar, INDEX-MATCH is more powerful.
I suspect, however, that VLOOKUP is better-known and more widely used. Here’s one indication: Google currently shows about 500,000 results for Excel VLOOKUP and only about 25,000 for Excel “INDEX MATCH”.
In the next several posts I’ll explain VLOOKUP. Then INDEX-MATCH. And along the way, I’ll explain the advantages and disadvantages of the two methods.
There are, of course, two additional lookup functions:
- HLOOKUP works just like VLOOKUP, but horizontally rather than vertically. So everything I say about VLOOKUP also applies to HLOOKUP.
- LOOKUP was designed for compatibility with Lotus 1-2-3; and 1-2-3’s @LOOKUP function was designed for compatibility with VisiCalc. So LOOKUP is less powerful than VLOOKUP. However, it does have one advantage over VLOOKUP, which I’ll in discuss in a later post in this series.
The VLOOKUP function has these arguments:
=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
The range_lookup argument is optional, often overlooked, but very useful. This is because range_lookup determines whether VLOOKUP returns an approximate match or an exact match.
Although Excel actually uses a different technique, here’s a simple way to think about how approximate matches work:
Excel works its way down the first column until it finds a value greater than the lookup value. Then it backs up one row to return its results. If the lookup value is greater than the last number in the column, it delivers that last result.
The only time an approximate match should be used is when you DEFINITELY need an approximate match. If you need an exact match, and if you want accurate results, NEVER use an approximate match.
By default, VLOOKUP returns an approximate match. This is unfortunate, for two reasons. First, in my experience, most Excel users need exact matches most of the time. Second, approximate matches are risky, because they can return false results.
This figure illustrates a typical occasion when approximate matches are needed.
The figure shows a schedule of quantity discounts. If a purchase is between 1 and 4 units, no discount is offered. A purchase from 5 through 24 units gets a 5% discount, and so on. And any purchase of 200 or more units gets a 20% discount.
The approximate-match feature allows us to look up the discount for an order of any size. Cell E4, for example, shows that the discount for a purchase of 7 units is 5%.
Here’s the formula for this calculation:
E4: =VLOOKUP(E$3,$A$3:$B$7,2,TRUE)
(The last argument is the optional range_lookup value. It can be omitted, because the default value for this argument is TRUE.)
The formula in cell E7 is similar. It shows that VLOOKUP works as you expect it to for large values. That is, any purchase in excess of the largest quantity will get the largest quantity discount.
Notice in the figure that the units are sorted in ascending order. This is an ABSOLUTE requirement. If your data isn’t sorted, you can get false results.
My next post will discuss additional dangers with approximate matches.
