Part II: Working with Formulas and Functions
Finding an approximate match with a lookup function does not find the closest match. Rather, it finds the largest
match that’s not larger than the lookup value, even if the next highest value is closer to the lookup value.
If the data in the lookup column isn’t sorted lowest to highest, you may not get an error, but you will likely get an
incorrect result. The lookup functions use a binary search to find an approximate match. A binary search basically
starts in the middle of the lookup column and determines whether the match will be in the first half or the second
half of the values. Then it splits that half in the middle and looks either forward or backward depending on the middle
value. That process is repeated until the result is found.
You can see with a binary search that unsorted values could cause the lookup function to choose the wrong half to
look in and return bad data.In the example in Figure 14.7, VLOOKUP stops at row 5 because 1,023 is the largest value in
the list that’s not larger than our lookup value of 2,003.89. The three sections of the for-
mula work as follows:■ (^) The first VLOOKUP returns the base amount in the third column, or 69.80.
■ The second VLOOKUP subtracts the “Wages over” amount (from the first column)
from the total wages.
■ The last VLOOKUP returns the percentage in the fourth column. This percentage is
multiplied by the “excess wages,” and the result is added to the base amount.
When all three VLOOKUP functions are evaluated, the formula computes as follows:
=69.80 + (2,003.89 – 1,023.00) * 15.0%
The method the lookup functions use to find an approximate match is much faster than an exact match. For an exact
match, the function has to look at every single value in the lookup column. If you know your data will always be sorted
lowest to highest and will always contain an exact match, you can decrease calculation time by setting the last argu-
ment to TRUE. An approximate match lookup will always find an exact match if it exists and the data is sorted.
Finding the closest match with the INDEX and MATCH functions
As with all of our lookup formulas, the INDEX and MATCH combination can be substituted.
Like VLOOKUP and HLOOKUP, MATCH also has a final argument to find approximate matches.
MATCH has the added advantage of being able to work with data that is sorted highest to
lowest (see Figure 14.8).
The VLOOKUP-based formula from Figure 14.7 returns #N/A as shown in cell D16 in Figure
14.8. This is because VLOOKUP looks at the middle of the lookup column, determines that
it is higher than the lookup value, and then looks only at values before the middle value.
Because our data is sorted in descending order, there are no values before the middle value
that are lower than the lookup value.