Loading exercise…
Loading exercise…
Exercises are built for a keyboard. Read the task here, then open this page on a laptop to solve it.
MATCH with an approximate row. MATCH(value, list, 1) returns the position of the largest item that is less than or equal to the value, if the list is sorted smallest to largest. Use it for the band and MATCH(…, 0) for the exact column header, then let INDEX return the crossing cell.
=INDEX($B$2:$D$6,MATCH(G2,$A$2:$A$6,1),MATCH(H2,$B$1:$D$1,0)) a tax rate by income band and filing status
Sales is testing a new discount grid where the rate depends on both order size and customer segment. Elena Petrova wants to see what North’s October orders would have received.
In D2:D26, return the discount from the grid H2:J4: the row is the highest “From” value in G2:G4 that the order value in C2:C26 reaches, the column is the segment in B2:B26 matched exactly against H1:J1. Write it once in D2 and fill down.
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Order | Segment | Value | Discount | From | SMB | Mid-Market | Enterprise | ||
| 2 | 50830 | Mid-Market | 710.37 | 0 | 0% | 0% | 2% | |||
| 3 | 50831 | SMB | 228.00 | 250 | 3% | 5% | 7% | |||
| 4 | 50834 | Mid-Market | 287.85 | 500 | 5% | 8% | 10% | |||
| 5 | 50848 | SMB | 35.20 | |||||||
| 6 | 50852 | SMB | 144.88 | |||||||
| 7 | 50854 | SMB | 203.20 | |||||||
| 8 | 50856 | SMB | 41.50 | |||||||
| 9 | 50858 | SMB | 135.80 | |||||||
| 10 | 50860 | SMB | 220.01 | |||||||
| 11 | 50862 | SMB | 50.16 | |||||||
| 12 | 50866 | Enterprise | 1,215.20 | |||||||
| 13 | 50869 | SMB | 62.80 | |||||||
| 14 | 50872 | Mid-Market | 622.00 | |||||||
| 15 | 50876 | Enterprise | 997.30 | |||||||
| 16 | 50878 | SMB | 125.00 | |||||||
| 17 | 50880 | Mid-Market | 301.00 | |||||||
| 18 | 50881 | SMB | 185.60 | |||||||
| 19 | 50882 | Mid-Market | 356.95 | |||||||
| 20 | 50896 | Mid-Market | 165.30 | |||||||
| 21 | 50897 | SMB | 55.00 | |||||||
| 22 | 50904 | SMB | 57.00 | |||||||
| 23 | 50906 | SMB | 303.25 | |||||||
| 24 | 50908 | Mid-Market | 565.10 | |||||||
| 25 | 50913 | SMB | 78.38 | |||||||
| 26 | 50915 | Mid-Market | 89.30 | |||||||
| 27 | ||||||||||
| 28 | ||||||||||
| 29 |
=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