Problem: I am using a lookup table to calculate a late-payment penalty. As soon as a customer is 1 day late, they are charged the penalty for the first month. When they reach 31 days late, they pay for two months. After 60 days late, they are billed for half months.
Strategy: Earlier in "Nest IF Statements", there was an example using the approximate version of VLOOKUP. This rare version would look for a match. When one is not found, it would return the row just smaller than the lookup value. In this case, you need the VLOOKUP to go the opposite way. VLOOKUP can not do that, but MATCH can.
Make sure that your penalty lookup table is sorted from high to low. (Hmmm, back in Fig 428, I should add rows for Credit Card Company Analysts and IRS Agents.)
-
Another rarity: the lookup table sorted descending.
You will see this calculation take shape after many intermediate steps. In real life, you could do all of these steps in a single formula.
Calculate a Penalty Row in F2 with =MATCH(E2,$J$3:$J$12,-1).
- Find the row with the appropriate penalty.
Take a look at the results of that formula. In row 5, the payment is 30 days late. There is an exact match in Fig 446 for 30 days late, so the formula returns the exact match. However, in rows 2 through 4, there is no exact match. Because the third argument of MATCH is -1, Excel is returning the result from the next higher row in the table. The 68 days late in F3 is matched to the 75 day penalty in row 7 of the table.
- Use INDEX to return the Penalty % from the table.
This is a third example of something that you can do with MATCH and INDEX that you can not do with a regular VLOOKUP.