Lookup With Approximate Match Versus SQL Range Join

A commission of 2% up to 10,000, 3% up to 50,000 and 4% beyond. A shipping price by weight band. A tax rate by income bracket. All of these are the same shape: a table of bands, a value, and a question about which band the value falls in. Excel solves it with an approximate-match lookup that most people learned once and never looked at again. SQL solves it with a range join that must be spelled out. This article lines the two up, shows where the defaults diverge and gives the rules that keep both honest. It sits with the other worked pairs in moving between Excel and SQL, and the exact-match version is in joining two sheets on two keys in Excel and in SQL.

Sample data

A commission band table, sorted by the lower threshold, in Bands and as a table bands(low, rate):

A band starts at its low and runs until the next band starts. A sale of 9,999 earns 2%, 10,000 earns 3%, 49,999 earns 3% and 50,000 earns 4%. The sheet and the query should agree on all four.

Excel: approximate match

XLOOKUP with match mode -1 finds the exact value or the next smaller one:

=XLOOKUP(D2, Bands[low], Bands[rate], "below first band", -1)

For 9,999 it returns 0.02. For 10,000 it finds an exact match and returns 0.03. For 120,000 it returns 0.04, because the next smaller threshold is 50,000. The older equivalents:

=VLOOKUP(D2, Bands, 2, TRUE)
=LOOKUP(D2, Bands[low], Bands[rate])
=INDEX(Bands[rate], MATCH(D2, Bands[low], 1))

All use approximate matching: the final TRUE in VLOOKUP and the 1 in MATCH. They require the lookup column sorted ascending. On an unsorted table, they return wrong answers with no warning, because they use a binary search that assumes order. XLOOKUP with mode -1 does not require sorting, though a binary search mode (2) does and is faster.

What the approximate match does not do is check an upper bound. A value of 120,000 falls into the last band and keeps the 4% rate, which may be right. If the last band should end at 100,000 and anything above needs a different treatment, the lookup will not tell you. A negative value returns "below first band" only because of the not-found argument; with VLOOKUP it returns #N/A.

SQL: a range join

The SQL needs an upper bound, which can be stored in the table or computed with lead():

WITH b AS (
  SELECT low,
         lead(low) OVER (ORDER BY low) AS high,
         rate
  FROM bands
)
SELECT s.sale_id, s.amount, b.rate, s.amount * b.rate AS commission
FROM sales s
LEFT JOIN b
  ON s.amount >= b.low
 AND (s.amount < b.high OR b.high IS NULL);

lead(low) gives each band the next band's start as its end, and the last band has NULL for high, meaning open-ended. The join condition is half-open: the lower bound is inclusive and the upper exclusive, so 10,000 belongs to the second band and 9,999 to the first. With a LEFT JOIN, a sale outside every band gets a NULL rate instead of vanishing.

A cleaner design stores both ends:

and the join is ON s.amount >= b.low AND (s.amount < b.high OR b.high IS NULL). Storing high explicitly lets you represent gaps and a closed top end deliberately.

The boundary: why half-open

BETWEEN low AND high is inclusive at both ends. With bands 0 to 10,000 and 10,000 to 50,000, a sale of exactly 10,000 matches both bands and the join returns two rows, doubling the sale in totals. Excel's approximate match never shows this problem because it picks one threshold. Half-open ranges (>= low AND < high) make every value belong to exactly one band. Use them everywhere, including in the table's documentation: "a band includes its lower limit and excludes its upper limit".

For decimal values, remember that "up to 10,000" as stated by a business usually means "up to and including 10,000", which is a closed upper bound. Convert the business statement to half-open form deliberately and check the boundary case with the person who wrote the rule: does a sale of exactly 10,000 get 2% or 3%?

Gaps and overlaps

The SQL behaviors are noisier, which is an advantage: gaps show up as NULLs and overlaps as inflated totals, and both can be tested. Excel's quietness is why a rate table with a gap produces plausible, wrong numbers for years.

Check the table itself. In SQL, find overlaps and gaps with a self join or a window:

SELECT a.low, a.high, b.low AS next_low
FROM bands a JOIN bands b ON b.low > a.low
WHERE a.high > b.low;                -- overlap

and gaps where a.high < next_low. Better, forbid them: PostgreSQL range types and an exclusion constraint do it.

CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE bands (
  span numrange NOT NULL,
  rate numeric(5,4) NOT NULL,
  EXCLUDE USING gist (span WITH &&)
);
INSERT INTO bands VALUES ('[0,10000)', 0.02), ('[10000,50000)', 0.03), ('[50000,)', 0.04);

numrange bounds use [ for inclusive and ) for exclusive, which is exactly the half-open convention. The EXCLUDE constraint rejects any row whose range overlaps an existing one. The lookup becomes JOIN bands b ON b.span @> s.amount. In Excel, a check column helps: =AND(B3=A4, ...) comparing each band's high to the next band's low.

Bands that change over time

Rates change. A band table with a valid_from date needs a second condition in the join so that each sale uses the rate in force at the sale date:

SELECT s.sale_id, s.amount, b.rate
FROM sales s
JOIN LATERAL (
  SELECT rate
  FROM bands
  WHERE amount_from <= s.amount
    AND valid_from <= s.sold_on
  ORDER BY valid_from DESC, amount_from DESC
  LIMIT 1
) b ON true;

The lateral subquery finds, for each sale, the latest effective rate whose threshold the amount meets. It states "the most recent applicable row", which is also what the approximate match means in Excel with a two-key setup. In a spreadsheet, the same logic needs a helper column for the effective table or a filter inside the lookup: =XLOOKUP(D2, FILTER(Bands[low], Bands[valid_from]<=E2), FILTER(Bands[rate], Bands[valid_from]<=E2), , -1) works when the table is sorted appropriately, and gets hard to read quickly. A rate table with history is a good reason to move the logic to the database.

Checking

Test the boundaries, which are where bugs live: the lowest value, each threshold exactly, one unit below and one above each, and a value far above the last band. For the sample bands, the expected rates are 0.02 at 0, 0.02 at 9,999, 0.03 at 10,000, 0.03 at 49,999, 0.04 at 50,000 and 0.04 at 1,000,000. Run both the sheet and the query over that list and compare. Count the rows of the joined result against the number of sales: any difference is a gap (fewer, with an inner join) or an overlap (more).

When bands should not be a lookup at all

If the bands are applied cumulatively, as in progressive tax where each slice is taxed at its own rate, a single lookup gives the wrong answer: the rate for the top band applies only to the part above its threshold. That needs a sum over slices (SUMPRODUCT over the differences in Excel, a join and LEAST/GREATEST arithmetic in SQL). If the thresholds are few and fixed, a CASE expression is clearer than a table. And if the bands encode business rules that change often, keep them in a table with an owner and a history, and test them. Microsoft documents XLOOKUP match modes and the PostgreSQL manual covers range types and exclusion constraints. For the exact-match form, see XLOOKUP examples that replace VLOOKUP pain.