Approximate Matching for Tier Tables

A tier table sorts continuous values into bands. Examples include tax brackets, shipping fees by weight, volume discounts, and letter grades by score.

You do not have a row for every possible value, only the starting threshold of each band. A score of 87 has no exact entry, but it falls in the band that starts at 80.

This is where approximate matching shines: it finds the right band instead of demanding an exact hit.

So far we used MATCH(value, range, 0) for an exact match. The third argument 0 means "find this value precisely or return #N/A".

For tiers we use match type 1 instead. It finds the largest value that is less than or equal to the lookup value. That is exactly how a band lookup should behave.

One firm rule: with match type 1 the threshold list must be sorted in ascending order.

Imagine a grading table. Column E holds the lower thresholds, sorted ascending: 0, 60, 70, 80, 90. Column F holds the labels: F, D, C, B, A.

A score of 0 to 59 is an F, 60 to 69 a D, and so on. We only store the start of each band, not every score.

Our goal: given a score in G1, return its letter grade.

Use approximate MATCH to find which band a score falls into. MATCH(G1, E2:E6, 1) with a score of 87 looks for the largest threshold at or below 87.

The thresholds are 0, 60, 70, 80, 90. The largest one not exceeding 87 is 80, sitting in position 4. So MATCH returns 4.

That position points to the correct band even though 87 itself is not in the list.

Now feed the position into INDEX over the label column F2:F6.

INDEX(F2:F6, MATCH(G1, E2:E6, 1)) takes position 4 and returns the fourth label, "B".

So a score of 87 correctly maps to grade B. Change G1 to 95 and MATCH returns 5, giving "A"; change it to 55 and MATCH returns 1, giving "F".

Approximate MATCH (type 1) requires ascending order in the lookup range. It assumes the data climbs, and stops as soon as it passes the lookup value.

If your thresholds are out of order, MATCH may stop too early and return a wrong, silently incorrect position, no error to warn you. Always sort the threshold column smallest to largest before relying on a tier lookup.

XLOOKUP can also match approximately. Its fifth argument, the match mode, accepts -1 for "exact match or the next smaller item", perfect for tier tables.

This finds the largest threshold at or below G1 and returns the matching label, without needing INDEX. It is often easier to read than INDEX-MATCH for band lookups.

Now a volume discount. Thresholds in E (quantity ordered): 0, 10, 50, 100. Discounts in F: 0%, 5%, 10%, 15%.

• Order of 7: largest threshold at or below 7 is 0, position 1, returns 0%.
• Order of 60: largest at or below 60 is 50, position 3, returns 10%.
• Order of 200: largest at or below 200 is 100, position 4, returns 15%.

One formula handles every quantity.

What if a value is smaller than every threshold? With approximate MATCH there is no value at or below it, so MATCH returns #N/A.

To avoid this, make sure the first threshold covers the floor (often 0), or wrap the formula in IFERROR to show a clear message when an input is out of range.

Watch for these tier-table traps:

• Unsorted thresholds: the number one cause of wrong-but-silent results.
• Using match type 0: forces an exact match and returns #N/A for any in-between value.
• Storing band ends instead of starts: type-1 MATCH expects the lower bound of each band, not the upper.
• Text thresholds: numbers stored as text break the comparison; keep them numeric.

You can combine approximate matching with the two-way technique. Imagine shipping cost by both weight band (rows) and zone band (columns).

Use one approximate MATCH (type 1) to find the weight row and another to find the zone column, then feed both into INDEX. Because both axes are sorted thresholds, each MATCH lands on the correct band.

This blends INDEX-MATCH-MATCH with tier logic for rich rate tables.

Confirm your understanding of approximate tier lookups.

For tier and band lookups:

• Store the lower threshold of each band, sorted ascending.
• Use MATCH(value, thresholds, 1) to find the band position (largest value at or below the input).
• Wrap it in INDEX(labels, ...) to return the band, or use XLOOKUP(..., -1) for the same result.

Cover the floor with a 0 threshold or use IFERROR for out-of-range inputs, and never leave the thresholds unsorted.