XLOOKUP Approximate Match in Excel: Price Bands, Tiers and Discounts
An order worth 750 has no row in a tier table, so an exact match fails. Learn XLOOKUP's match mode of minus one, which takes the exact value or the next one down, and use it to find the discount tier for every order in Excel.
Try it yourself
Copy these tables into a blank sheet, then follow the steps below.
Paste at cell A1:
| From | Tier | Discount |
|---|---|---|
| $0 | Bronze | 0% |
| $500 | Silver | 5% |
| $1,000 | Gold | 10% |
| $5,000 | Platinum | 15% |
Paste at cell E1:
| Order | Value | Tier | Discount |
|---|---|---|---|
| ORD-1 | $750 | ||
| ORD-2 | $1,200 | ||
| ORD-3 | $300 | ||
| ORD-4 | $6,000 |
Step by step
- Approximate match. An order is worth 750, but the tier table has no row for 750, so an exact match fails. XLOOKUP has an approximate match for this.
- The tiers. The tier table is in A1:C5. Each row is where a band starts: Bronze from 0, Silver from 500, Gold from 1000 and Platinum from 5000. Column C holds each tier's discount.
- The orders. The orders are in E1:H5. There are 4 orders with their values in column F. We want the tier in column G and the discount in column H.
- Exact match fails. First try a normal XLOOKUP in G2. It looks for the order value F2 in the band starts $A$2:$A$5 and returns the tier from $B$2:$B$5. It returns #N/A because 750 isn't in the list. What we want is the biggest band start below 750.
G2
=XLOOKUP(F2,$A$2:$A$5,$B$2:$B$5) - Match mode. Add the match mode. Leave the fourth part (if not found) empty with an extra comma, then type -1. -1 means exact match, or the next smaller value. G2 now shows Silver. 750 sits between 500 and 1000, so it takes the band that starts at 500. Fill the formula down to G5. 1200 is Gold, 300 is Bronze and 6000 is Platinum. The $ signs keep the tier table fixed while F2 changes for each row.
G2
=XLOOKUP(F2,$A$2:$A$5,$B$2:$B$5,,-1) - The discount. For the discount, use the same formula in H2 but return the discount column: $C$2:$C$5 instead of $B$2:$B$5. Fill it down to H5, so every order gets its discount. Format H2:H5 as 0% so the discounts show as percentages.
H2
=XLOOKUP(F2,$A$2:$A$5,$C$2:$C$5,,-1) - Keep bands sorted. One rule for the tier table: each row in A2:A5 is the start of its band. Listing the bands from small to large keeps the table easy to read and check.
- Match mode -1. For bands and tiers, use match mode -1. XLOOKUP takes the exact value or the next one down, with no helper columns and no nested IF formulas.
Common mistakes
- Leaving out the empty comma. =XLOOKUP(F2,$A$2:$A$5,$B$2:$B$5,-1) puts -1 in the fourth part, which is what to show when nothing is found. So the cell shows -1 instead of a tier. Type two commas before -1 so it lands in the fifth part, the match mode.
- Using 1 instead of -1. Match mode 1 means exact match or the next larger value. 750 would then get Gold instead of Silver. For tables that list where each band starts, use -1.
- Listing band ends instead of band starts. If the table lists 499, 999 and so on, -1 rounds down to the wrong band. Put the first value of each band in the lookup column, and start the first band at 0 so small orders don't return #N/A.
- Forgetting the $ signs on the tier table. Without $A$2:$A$5, the range moves down by 1 row each time you fill down, and the lower rows get wrong tiers or #N/A. Lock the table ranges and leave F2 unlocked.
When a business analyst uses this
When writing acceptance criteria for a pricing rule, put the band starts and discounts from the workshop in a small table. Then use XLOOKUP with -1 to work out the expected tier for each test order, including edge values like 499 and 500, so the business can check the results before sign-off.
Questions people ask
How do you do an approximate match in Excel?
Use XLOOKUP with a match mode as the fifth part. -1 returns the exact value or the next smaller one, as in =XLOOKUP(F2,$A$2:$A$5,$B$2:$B$5,,-1).
Does XLOOKUP use exact match by default?
Yes. If you leave out the match mode, XLOOKUP only takes an exact match and returns #N/A when the value isn't found. Add -1 as the fifth part when you want the next smaller value.
Can XLOOKUP replace INDEX and MATCH?
In most cases, yes. XLOOKUP can look left or right and do approximate matches in a single function, where INDEX and MATCH need two. INDEX and MATCH are still useful in Excel versions that don't have XLOOKUP.
