• Excel Book Excerpt

Excel Return the Next Larger Value in a Lookup

This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Return the Next Larger Value in a Lookup

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 Figure 423, I should add rows for Credit Card Company Analysts and IRS Agents.)

LE10000442.jpg

Figure 439 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).

LE10000446.jpg

Figure 440 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 Figure 439 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.

LE10000445.jpg

Figure 441 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.

<-Previous Topic Next Topic->

For more resources for Microsoft Excel:

privacy policy