A price-break table lists only the quantity where each discount starts, so most orders fall somewhere between its rows and an exact lookup finds nothing. XLOOKUP's match mode can fall back to the next smaller threshold instead, and that threshold is the start of the band the order belongs to.
You handle order entry at a packaging wholesaler. Trade customers earn a volume discount that steps up with the number of packs on an order, and the price-break table in F1:G6 says where each step starts: no discount from 1 pack, 5% from 50, 8% from 100, 12% from 250 and 15% from 500. Today's orders are in A2:C7. In D2, use XLOOKUP to find the discount for the pack count in B2 and return the order's net value, packs times list price less that discount, then copy it down to D7.
Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Order | Packs | List Price ($) | Net Value ($) | Packs From | Discount | |
| 2 | SO-2207 | 40 | 12.5 | 1 | 0 | ||
| 3 | SO-2208 | 50 | 12.5 | 50 | 0.05 | ||
| 4 | SO-2209 | 180 | 9.8 | 100 | 0.08 | ||
| 5 | SO-2210 | 250 | 9.8 | 250 | 0.12 | ||
| 6 | SO-2211 | 499 | 6.2 | 500 | 0.15 | ||
| 7 | SO-2212 | 720 | 6.2 |
A price-break table records only where each band begins, so almost no real order size is in it. An exact-match XLOOKUP finds SO-2208's 50 packs and returns #N/A for the other five orders. A match mode of -1 changes the question from "where is 180?" to "what is the largest threshold that is not more than 180?", and the answer, 100, is the start of the band 180 belongs to, so it returns 8%. That is why the table is written as "Packs From" rather than as ranges: each number is the first quantity that earns its rate, and the next smaller one always names the right band. The boundaries are where this earns its keep. SO-2208 at exactly 50 matches 50 and gets 5%, and SO-2210 at exactly 250 gets 12%, because an exact match is still taken first. SO-2211 at 499 is one pack short of 500, falls back to 250 and earns 12%, not 15%, which is the order a sales rep would ring about adding one more pack. The empty fourth argument is safe only because the table starts at 1; if it started at a 50-pack minimum, a 40-pack order would have no smaller threshold to fall back to, and if_not_found is where you would say so. Finally, (1 - rate) turns "12% off" into "88% paid", so one multiplication by packs and list price gives the net value without a separate discount column. The $ signs hold the table still while B2 and C2 move down with each order.