Loading exercise…
Loading exercise…
Exercises are built for a keyboard. Read the task here, then open this page on a laptop to solve it.
Approximate match: lookup bands. For bands, you don’t want an exact match. You want the last threshold the value has reached. XLOOKUP does this with match_mode -1 (exact or next smaller). VLOOKUP does it with TRUE as the last argument, but only if the thresholds are sorted from smallest to largest.
=XLOOKUP(B2,$E$2:$E$5,$F$2:$F$5,,-1) shipping fee by parcel weight band
Brightline’s volume discount gives 5% from 250 and 10% from 500 per order. Daniel Ortiz wants to see which tier each West order from July would have landed in.
In C2:C20, return the discount rate from the tier table in F2:G4 for each order value in B2:B20. An order belongs to the highest tier whose “From” value it reaches. Write it once in C2 and fill down.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Order | Value | Discount | From | Discount | |||
| 2 | 50547 | 180.40 | 0 | 0% | ||||
| 3 | 50552 | 195.75 | 250 | 5% | ||||
| 4 | 50554 | 84.00 | 500 | 10% | ||||
| 5 | 50565 | 252.00 | ||||||
| 6 | 50573 | 513.00 | ||||||
| 7 | 50576 | 195.63 | ||||||
| 8 | 50577 | 123.50 | ||||||
| 9 | 50582 | 96.93 | ||||||
| 10 | 50584 | 92.40 | ||||||
| 11 | 50585 | 39.73 | ||||||
| 12 | 50587 | 286.50 | ||||||
| 13 | 50588 | 271.58 | ||||||
| 14 | 50590 | 76.50 | ||||||
| 15 | 50603 | 207.00 | ||||||
| 16 | 50608 | 329.20 | ||||||
| 17 | 50615 | 22.00 | ||||||
| 18 | 50616 | 122.82 | ||||||
| 19 | 50624 | 506.18 | ||||||
| 20 | 50631 | 153.00 | ||||||
| 21 | ||||||||
| 22 | ||||||||
| 23 |
=IF(B2>=1.5,SUM(C2:C9),0)Language
Full plan
Excel ✓ · SQL and Power BI also included
Cancel anytime · 14-day full refund · Secure payment by Stripe