Work out shipping cost from a shipping method, without a lookup table
SWITCH matches a value against a short list of options right inside the formula — no helper table needed for three shipping tiers.
Task:
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.
Learning Objectives:
- Match a value against a short list of named options with SWITCH
- Choose SWITCH over a lookup table when the options are few and fixed
- Turn a matched rate into a cost with a single multiplication
Solve without hints for +5 XP
Interactive Spreadsheet
The data in this exercise
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 |
What this exercise teaches (contains the answer)
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.