How to Use MATCH for Approximate Matches in Excel

Place a number into the right band of a sorted threshold table — loyalty tiers, bands and brackets — with MATCH type 1.

You will learn to

• Find which band a number falls in with MATCH(…,1)
• Return a value for that band with INDEX
• Look one band ahead to measure the gap to the next tier

How it works

With match_type 1, MATCH looks for the largest value that is less than or equal to the lookup value. In a table of thresholds sorted smallest to largest — 0, 10, 25, 50 — =MATCH(37,A2:A5,1) returns 3 because 37 falls between 25 and 50. The list must be sorted ascending for this to work.

Combine it with INDEX to return the tier’s name or discount: =INDEX(C2:C7,MATCH(F2,A2:A7,1)). Add 1 to the position to look at the next tier up, which is handy for messages like “13 more nights to reach Platinum”. For lists sorted largest to smallest, use match_type -1.

=INDEX(C2:C7,MATCH(F2,A2:A7,1))