How to join on the closest match with an approximate lookup

What approximate match means

“Approximate” is a misleading name. It doesn't find something similar; it finds the threshold a value falls under. A discount table that says 0% from £0, 5% from £1,000 and 10% from £5,000 has three rows, and an order of £3,200 should land on the £1,000 row: the largest threshold not above the value.

Note that SO-503 at exactly £5,000 gets the 10% tier: an exact hit on a threshold counts. If your rules say “over £5,000”, store the threshold as 5000.01 or adjust the logic.

Excel and Google Sheets formulas

With the order value in B2 and the tiers in Tiers!A2:B5, any of these return the discount:

=XLOOKUP(B2, Tiers!A2:A5, Tiers!B2:B5, "below lowest", -1)
=VLOOKUP(B2, Tiers!A2:B5, 2, TRUE)
=INDEX(Tiers!B2:B5, MATCH(B2, Tiers!A2:A5, 1))

• XLOOKUP with -1 means exact match or next smaller item. It works on unsorted thresholds and lets you set a not-found message. Use 1 for “exact or next larger”, for example to round a parcel weight up to the next price band.
• VLOOKUP with TRUE (or the fourth argument omitted) and MATCH with 1 use a binary search. They assume the first column is sorted ascending; on unsorted data they return a wrong row without any error.
• LOOKUP (the old function) behaves the same way and has the same sorting requirement.

The most common bug is forgetting VLOOKUP's fourth argument when you wanted an exact match. Omitted means TRUE, so an ID lookup quietly returns the nearest smaller ID. See VLOOKUP vs XLOOKUP vs INDEX MATCH.

Why sorting matters

Binary search halves the list each step, assuming everything to the left is smaller. If thresholds are 0, 5000, 1000, 20000, a value of 3200 may be compared with 5000 first, sent left, and settle on 0. You get 0% instead of 5%, and nothing tells you. Sort the threshold column ascending (Data → Sort) whenever you use VLOOKUP TRUE or MATCH 1, or switch to XLOOKUP.

XLOOKUP does have a binary search option (search mode 2 or -2) for very large tables, and that one needs sorted data too. The default search mode 1 scans the whole list and is safe on any order.

Closest match per key: ASOF joins

Sometimes the threshold depends on another key. Stock prices by ticker, a customer's credit limit changing over time, the most recent exchange rate before each invoice. You need the latest row for that key at or before each value. In Excel you combine FILTER with XLOOKUP; in SQL you can use an ASOF join, which DuckDB supports:

SELECT i.invoice_id, i.currency, i.invoice_date, i.amount,
       r.rate, i.amount * r.rate AS amount_gbp
FROM invoices i
ASOF LEFT JOIN fx_rates r
  ON i.currency = r.currency
 AND i.invoice_date >= r.rate_date;

The equality condition picks the series and the inequality picks the nearest earlier row. Databases without ASOF can do it with a correlated subquery or ROW_NUMBER() over the candidate rows. pandas has merge_asof, which requires both frames to be sorted on the on column and supports by= for the key and direction='backward' (the default), 'forward' or 'nearest'.

Analistable uses DuckDB in the browser, so a question such as “convert each invoice at the most recent rate on or before its date” can be answered with this join without uploading either sheet.

Nearest in either direction

If you want the truly closest value, above or below, neither -1 nor 1 is enough. In Microsoft 365, compute the absolute difference and take the smallest:

=XLOOKUP(MIN(ABS(Targets!A2:A50-B2)), ABS(Targets!A2:A50-B2), Targets!B2:B50)

Ties go to the first row found. Older versions need this entered as an array formula with Ctrl+Shift+Enter. For date ranges with explicit start and end columns, see joining on dates and date ranges, and for the wider picture the join spreadsheets guide.

Keeping band tables trustworthy

• Start the table at the lowest possible value (0 for money, or a very early date) so nothing falls below the first row and returns #N/A.
• Store thresholds as numbers, not text such as "£1,000". Text thresholds compare alphabetically and break the search.
• Write the rule in words next to the table: "from" means inclusive. Disputes about edge cases are usually about this.
• Test each boundary: look up exactly 1000, 999.99 and 1000.01 and confirm the tiers you expect.

When bands change over time, for example a new discount scheme from January, add a valid-from date and combine this technique with a date-range lookup rather than overwriting the old bands, so past orders still price correctly.

Frequently asked questions

Why does my approximate lookup return #N/A?

The value is smaller than the first threshold. Add a row for the lowest possible value, such as 0, or supply an if-not-found value in XLOOKUP.

Does XLOOKUP approximate match need sorted data?

No. Match mode -1 or 1 with the default search mode works on unsorted data. Only the binary search modes (2 and -2) need sorting.

Why does my VLOOKUP TRUE return the wrong tier?

The first column isn't sorted ascending, or it contains text numbers mixed with real numbers. Sort it and make the types consistent.

What is an ASOF join?

A join that matches each row to the most recent row in another table at or before its value, usually a date, optionally within the same key. DuckDB supports it with ASOF JOIN.

How do I round up to the next band instead?

Use XLOOKUP with match mode 1 (exact match or next larger item), for example to find the parcel price band for a weight.

Related guides

• VLOOKUP vs XLOOKUP vs INDEX MATCH: which should you use?
• How to join on dates and date ranges
• How to join spreadsheets on a common column
• How to join two tables on two columns at once
• How to join two spreadsheets on a common column