Stockertim Dictionary · Technology
Approximate match
Also called range lookup, TRUE match type, next smaller match
In short
A lookup setting that returns the nearest lower value in a sorted list, suited to bands and tiers but wrong for codes and names.
- Intermediate
- Concept
- Checked
In detail
In approximate mode a lookup does not need to find the value itself. It finds the largest entry that is less than or equal to the lookup value and returns the matching item. That suits tables of bands, where the first column holds the lower edge of each band: 0, 200 and 400 for a commission scale, or the starting points of grade boundaries or delivery charge tiers.
VLOOKUP and MATCH use approximate matching when their final argument is TRUE, 1 or left out. XLOOKUP needs it requested: a match mode of -1 returns an exact match or the next smaller item, and 1 returns an exact match or the next larger item. A lookup of 250 against band starts of 0, 200 and 400 lands on the 200 band, and anything from 400 upwards lands on the top band.
The method relies on the first column being sorted in ascending order. VLOOKUP and MATCH search the list by repeatedly halving it, so on an unsorted list they can return a wrong row without any error message. XLOOKUP's next-smaller mode copes with unsorted data, but a sorted band table is still easier to read. A lookup value below the first band returns #N/A, which is why band tables usually start at zero.
Applied to identifiers, approximate matching is dangerous: a missing product code returns the price of a neighbouring code instead of an error. The setting belongs to banded scales only.
Worked example
A courier in Lusaka charges by weight band: from 0 kg the fee is 50, from 5 kg it is 80 and from 20 kg it is 150.
- A 12 kg parcel: the largest band start not above 12 is 5, so the fee is 80
- A 20 kg parcel matches the 20 band exactly, so the fee is 150
- Two 12 kg parcels together: 80 × 2 = 160
Approximate match places each weight in its band without a nested IF.
In practice
Band tables with approximate matching handle tax-style scales, discount tiers, grading, commission and shipping charges, with thresholds kept in cells that can be changed without touching formulas.
Watch out
- VLOOKUP and MATCH default to approximate match, which gives wrong results on codes.
- The band column must be sorted ascending for VLOOKUP and MATCH.
Learn it properly
Official sources
Facts last checked on 9 November 2026.
Education, not advice: the Stockertim Dictionary explains what terms mean. It never recommends buying or selling anything.