Chapter 14: Using Formulas for Matching and Lookups
14
Finding the closest match from a list of banded values
The VLOOKUP, HLOOKUP, and MATCH functions allow the data to be sorted in any order.
Each of them has a final argument that will force the function to find an exact match or
return an error if it cannot.
These functions also work on sorted data for the times when you want only an approximate
match. Figure 14.7 shows a method for calculating income tax withholding. The withhold-
ing table doesn’t have every possible value; rather, it has bands of values. You first deter-
mine into which band the employee’s pay falls, and then you use the information on that
row to compute the withholding in cell D16.
=VLOOKUP(D15,B3:E10,3,TRUE)+(D15-
VLOOKUP(D15,B3:E10,1,TRUE))*VLOOKUP(D15,B3:E10,4,TRUE)FIGURE 14.7
Computing income tax withholding
The formula uses three VLOOKUP functions to get three pieces of data from the table. The
final argument for each VLOOKUP formula is set to TRUE, indicating that we want only an
approximate match.
To get a correct result when using a final argument of TRUE, the data in the lookup column
(column B in Figure 14.7) must be sorted lowest to highest. VLOOKUP looks down the first
column and stops when the next value is higher than the lookup value. In that way, it finds
the largest value that is not larger than the lookup value.