Skip to content

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.

  1. A 12 kg parcel: the largest band start not above 12 is 5, so the fee is 80
  2. A 20 kg parcel matches the 20 band exactly, so the fee is 150
  3. 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.

English