Finding the next largest match with the Excel MATCH function

Bill Jelen

The VLOOKUP function in Microsoft Excel is great at finding exact matches or the value just less than the lookup value. However, in certain situations, you might need to find the next largest record. VLOOKUP cannot do this, but the MATCH function can.

Say that you work for a rental company. Rental periods for 0 through 6 days are rounded up to the next highest day. Rental periods for days 7 through 28 are rounded up to the next highest week. Rental periods for 29 through 365 days are rounded to the next highest month.

You might initially set up a range lookup table such as this one:

range lookup table
    Figure 1

However, for this trick to work, the table needs to be sorted in descending order, as shown here:

sorted range lookup table
   Figure 2

Instead of VLOOKUP, you will use the MATCH function. While most people have not used MATCH, it is very similar to VLOOKUP. The lookup value is the same as VLOOKUP. The lookup table has to be only a single row or column instead of a rectangular table. You don't have to specify the column number. The last argument can be 0 (equivalent to FALSE in VLOOKUP), 1 (equivalent to TRUE in VLOOKUP) or -1 (no equivalent in VLOOKUP -- returns a value just larger).

Here is the oddity: MATCH returns the relative position of the matching row. In Figure 3, the formula =MATCH(H6,A2:A24,-1) in cell I6 returns an 11 because the matching row is in A12, which is the 11th item in the lookup range.

MATCH function returns relative position
Figure 3

You might wonder, what good is knowing that the match is in the 11th cell in the range? You need to use the MATCH as the second argument in the INDEX function.

The INDEX function takes a range of possible answers and an index number. =INDEX(B2:B24,11) would return the value from B12. =INDEX(B2:B24,MATCH(H6,A2:A24,-1) returns the Rental value. In order to make the formula able to be copied, use = INDEX(B$2:B$24,MATCH(H6,$A$2:$A$24,-1) in I4 and copy to I4:J6.

INDEX function
Figure 4

About the author:
Bill Jelen, aka MrExcel, has been a Microsoft Excel consultant for over twenty-five years. Read more of his expert Excel tips and tricks at his website, MrExcel.com.

View the next item in this Essential Guide: Creating a year-over-year report using an Excel pivot table or view the full guide: Ultimate Excel finance guide

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: