Commission tiers, tax brackets, shipping bands, volume discounts — these all need “find the band this number falls into,” not an exact match. XLOOKUP with match_mode -1 (exact or next smaller) lands a value in the right tier with no nested IFs.
D2 against tier breakpoints in A2:A5:
match_mode -1 means “exact match, or the next smaller breakpoint” — exactly how a bracket works.
The example
A commission table. Each row is the minimum sales to earn that rate. We look up $83,500.
| A | B | D | E | ||
|---|---|---|---|---|---|
| 1 | Sales at least | Rate | Sales | Rate | |
| 2 | $0 | 2% | $83,500 | 6% | |
| 3 | $25,000 | 4% | |||
| 4 | $50,000 | 6% | |||
| 5 | $100,000 | 8% |
The formula
The rate formula in E2:
How it works
The fifth argument is what makes this work:
D2($83,500) is the value we’re placing into a tier.- XLOOKUP searches the breakpoints
A2:A5for an exact match. There isn’t one. - Because
match_modeis-1, it falls back to the next smaller breakpoint —$50,000. - It returns the rate on that row from
B2:B5—6%. The fourth argument is left blank (noif_not_foundneeded for a tiered table that starts at 0).
No sorting headache. Unlike VLOOKUP’s approximate mode, XLOOKUP’s match_mode -1 does not require the table to be sorted (sorting is only needed if you also switch on binary search_mode). A breakpoint table is naturally in order anyway.
Try it: interactive demo
Drag the sales amount and watch which tier match_mode -1 lands on.
Variations
Legacy VLOOKUP version
In Excel 2019 and older, VLOOKUP’s approximate match does the same job — but the table must be sorted ascending:
Marginal (progressive) tax, not flat rate
A true progressive tax taxes each band at its own rate. That needs a running cumulative column and SUMPRODUCT — a flat “which bracket” lookup like the one above gives the marginal rate, not the total tax.
Pitfalls & errors
Wrong tier returned. Using the default match_mode 0 (exact) returns #N/A for any in-between value. You must set match_mode -1 for bracket lookups.
VLOOKUP version silently wrong if unsorted. VLOOKUP(…, TRUE) assumes the breakpoints are sorted ascending. Out-of-order rows return the wrong rate with no error. XLOOKUP -1 has no such requirement.
Below the lowest breakpoint. If a value is smaller than your first breakpoint, XLOOKUP -1 returns #N/A. Start the table at 0 (or add an if_not_found value) to cover everything.
Practice workbook
Frequently asked questions
How do I look up a tax bracket or commission tier in Excel?
What does match_mode -1 do in XLOOKUP?
Does the table need to be sorted for XLOOKUP -1?
How is this different from a progressive tax calculation?
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