(Part 2 of 5. See Part 1. See Part 3.)
Your data MUST be sorted when you use approximate matches. This is true for all Excel lookup functions that return approximate matches.
The reason for this requirement is that Excel doesn’t just work its way down a column or across a row for approximate matches. Instead, Excel uses a binary search technique. That is, Excel checks the middle of the list. If that value is less than the lookup value, it jumps to the middle of the following section; if the value is less, it jumps to the middle of the preceding section. It continues to halve the distance until it finds the correct result.
This technique is much faster than searching down a row or column, but it can produce unexpected results if the data isn’t sorted.
Often, if you do an approximate match against unsorted data, an Excel lookup function will return an error value. That’s the best result because it alerts you to a problem.
An approximate match against unsorted data can generated false results, like these.
But unfortunately, when Excel performs an approximate match against unsorted data, it can produce false results. For example, this figure illustrates two types of incorrect results.
The SKU #1 example searches for a value that’s not in the table. The two formulas in the example are:
F3: =VLOOKUP(F$2,$A$4:$C$8,2,TRUE)
F4: =VLOOKUP(F$2,$A$4:$C$8,3,TRUE)
The VLOOKUP function has these arguments:
=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
Because the range_lookup value is TRUE in cells F3 and F4, these formulas are looking for approximate matches.
As you can see in the figure, the two formulas return results for SKU-A101 instead. This is a significant problem, because Excel returns a false result, and that’s dangerous.
The SKU #2 example uses similar formulas to search for a value that IS in the table. But it also returns values for SKU-A101 instead of the correct result.
So the two main conclusions are obvious:
1. Use approximate matches only when you must.
2. Be CERTAIN your data is sorted when you use approximate matches.
In my next post, I’ll move on to exact matches with VLOOKUP.
{ 1 comment }
OUTSTANDING!!! This is really very cool.
You have pulled back the curtain of ignorance to reveal a dark, dank secret that has eluded so many for too long.
Please keep up this kind of investigative journalism. The World needs to know more.