XLOOKUP Approximate (Next Smaller/Larger)

Excel Formulas › Lookup

365 / 2021XLOOKUP

For tier tables and bands, XLOOKUP’s match mode finds the nearest value instead of an exact one — -1 for the next smaller, 1 for the next larger — and the data doesn’t even need to be sorted.

Quick formula: find the commission rate for a sales figure using a tier table:
=XLOOKUP(A2, thresholds, rates, , -1)
The 5th argument is match mode: -1 = exact or next smaller — exactly what tier tables need.

Functions used (tap for the full reference guide):

XLOOKUP

The example

$32,000 falls in the $25,000 tier (next smaller).

AB
1Tier fromRate
2$25,0006%
3$50,0008%

The formula

Match mode picks the nearest tier:

=XLOOKUP(A2, thresholds, rates, , -1) // -1 = exact or next smaller

How it works

The 5th argument controls how XLOOKUP matches:

  1. Leave the 4th argument (if_not_found) empty or set it, then add the match mode: 0 exact (default), -1 exact-or-next-smaller, 1 exact-or-next-larger.
  2. For tier/band lookups, -1 returns the rate for the highest threshold not exceeding the value.
  3. Unlike VLOOKUP’s approximate mode, XLOOKUP doesn’t require the list to be sorted (though sorting is still tidy).
  4. Use 1 when you want to round up to the next bracket instead.

Safer than VLOOKUP TRUE. VLOOKUP’s approximate match silently misbehaves on unsorted data; XLOOKUP’s explicit match mode is clearer and more forgiving. It’s the modern way to do bracket lookups (tax, commission, shipping).

Try it: interactive demo

Live demo

Sales → tier rate (next smaller).

Rate:

Variations

Next larger

Round up to the bracket:

=XLOOKUP(A2, thresholds, rates, , 1)

Exact (default)

Omit match mode:

=XLOOKUP(A2, ids, names)

VLOOKUP equivalent

Approximate (needs sorting):

=VLOOKUP(A2, table, 2, TRUE)

Pitfalls & errors

Mind the empty 4th argument. Match mode is the 5th argument — include a comma for the (skipped) if_not_found: XLOOKUP(v, l, r, , -1).

Thresholds are lower bounds. With -1, list the start of each band; the value maps to the highest threshold ≤ it.

365/2021 only.

Practice workbook

📊
Download the free XLOOKUP Approximate (Next Smaller/Larger) practice workbook
XLOOKUP approximate-match examples (formula text + result) with next-larger and VLOOKUP variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I do an approximate match with XLOOKUP?
Add the match-mode argument: =XLOOKUP(value, thresholds, results, , -1) finds the exact or next-smaller value — ideal for tier tables. Use 1 for next-larger.
Does XLOOKUP need the data sorted for approximate match?
No — unlike VLOOKUP's TRUE mode, XLOOKUP's match modes work on unsorted data, though sorting keeps tier tables readable.
Where does the match mode go?
It's the fifth argument, after if_not_found. Include a comma for the skipped fourth argument: =XLOOKUP(v, l, r, , -1).

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Tax bracket lookup · Tiered commission · XLOOKUP if not found

Function references: XLOOKUP