Chapter 14: Using Formulas for Matching and Lookups
14
FIGURE 14.8
The same withholding table as Figure 14.7 except that the data is sorted in descending order
The INDEX and MATCH formula in cell D18 of Figure 14.8 returns the correct result and is
shown here:
=INDEX(B3:E10,MATCH(D15,B3:B10,-1)+1,3)+(D15-
INDEX(B3:E10,MATCH(D15,B3:B10,-1)+1,1))*INDEX(B3:E10,MATCH
(D15,B3:B10,-1)+1,4)The final argument of MATCH can be −1, 0, or 1.
■ (^) −1 is used for data that is sorted highest to lowest. It finds the smallest value in
the lookup column that is larger than the lookup value. There is no equivalent
method using VLOOKUP or HLOOKUP.
■ (^) 0 is used for unsorted data to find the exact match. It is equivalent to setting the
final argument of VLOOKUP or HLOOKUP to FALSE.
■ (^) 1 is used for data that is sorted lowest to highest. It finds the largest value in the
lookup column that is smaller than the lookup value. It is equivalent to setting the
final argument of VLOOKUP or HLOOKUP to TRUE.
With a final argument of −1, MATCH finds a value that is larger than the lookup value, and
the formula adds 1 to the result to get the proper row.
Looking up values from multiple tables
Sometimes the data that you want to look up can come from more than one table depend-
ing on a choice that the user makes. In Figure 14.9, a withholding calculation similar to