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.