SWITCH matches a value against a short list of options right inside the formula — no helper table needed for three shipping tiers.
You handle order fulfillment at Larkspur Home Goods, a small online furniture store. Every order ships by one of three methods — Standard, Express or Overnight — each billed at a flat rate per kilogram: $2, $5 and $12. There are only three methods and they rarely change, so nobody wants a lookup table for them. In column D, work out each order's shipping cost directly from the method and weight.
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 | |
|---|---|---|---|---|
| 1 | Order | Method | Weight (kg) | Shipping Cost |
| 2 | ORD-201 | Standard | 4 | |
| 3 | ORD-202 | Express | 2.5 | |
| 4 | ORD-203 | Overnight | 1 | |
| 5 | ORD-204 | Standard | 7 | |
| 6 | ORD-205 | Express | 3 |
SWITCH(B2,"Standard",2,"Express",5,"Overnight",12) compares B2 against each option in turn and returns the rate beside whichever one matches — 2 for Standard, 5 for Express, 12 for Overnight — without a reference table anywhere on the sheet. That is the right trade when the list of options is small and stable: a VLOOKUP against three rows tucked in a corner of the sheet would work too, but it means maintaining a table nobody ever needs to see just to hold three numbers. Multiplying the result by C2 turns a per-kg rate into an actual cost for that order's weight, exactly as a rate looked up from a table would.