Logical FunctionsIntermediate

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.

Interactive Spreadsheet

Hints

+5 XPno-hints bonus

Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.

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.

6 rows × 4 columns5 cells you fill in
ABCD
1OrderMethodWeight (kg)Shipping Cost
2ORD-201Standard4
3ORD-202Express2.5
4ORD-203Overnight1
5ORD-204Standard7
6ORD-205Express3
What this exercise teachesMay contain 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.