Challenge Scenario
In logistics and order fulfillment, delivery fees depend on the customer's delivery destination zone and the weight of the shipment. Rates are maintained in a central carrier tariff table.
As a Logistics Coordinator, you are processing daily package dispatches:
- Freight Rate Lookup (Column D): Look up the per-kilogram rate for each order from the Rate Table (
G2:I4) by matching the order's Zone Code.
- Total Shipping Cost (Column E): Multiply package
Weight (kg) by the retrieved Rate / kg.
- Dispatch Weight Aggregate (Cell B8): Calculate the total combined weight of all customer orders in Column C.
- Total Freight Expenditure (Cell B9): Calculate the grand total shipping cost across all customer orders in Column E.
Your goal is to build dynamic lookup, arithmetic, and summary aggregate formulas.
Your Sheet Layout
The shipping workbook features a primary order dispatch table on the left, executive summary KPIs below, and a master tariff reference table on the right:
| Cell Range |
Column Name |
Section |
Formula / Approach |
What It Does |
A2:A5 |
Order ID |
Order Details |
Unique order tracking code (ORD-101 to ORD-104) |
Read-only transaction identifiers. |
B2:B5 |
Zone Code |
Order Details |
Destination zone strings (Z-01, Z-02, Z-03) |
Lookup keys used to retrieve carrier freight rates. |
C2:C5 |
Weight (kg) |
Order Details |
Billed parcel weight values in kilograms |
number for cost calculation and aggregate weight. |
G2:I4 |
Zone Code, Carrier, Rate / kg |
Rate Lookup Table |
Master shipping rate table locked with absolute references |
Lookup source table for carrier shipping rates. |
D2:D5 |
Rate / kg |
Looked Up Rate |
=VLOOKUP(B2, $G$2:$I$4, 3, FALSE) |
Extract delivery rate per kg based on Zone Code. |
E2:E5 |
Total Shipping |
Total Cost |
=C2 * D2 |
Multiply weight by rate to find total shipping cost. |
B8 |
Total Weight (kg) |
Summary Total |
=SUM(C2:C5) |
Sum total weight dispatched across all orders. |
B9 |
Total Shipping Cost ($) |
Summary Total |
=SUM(E2:E5) |
Sum total freight invoice charges. |
Complete the objectives below in the live spreadsheet editor. Each objective card will turn green once your formula evaluates successfully.
1
In Column D (D2:D5), look up Rate / kg from the Rate Table (G2:I4) based on Zone Code using VLOOKUP or XLOOKUP.
2
In Column E (E2:E5), calculate Total Shipping by multiplying Weight (kg) by Rate / kg.
3
In cell B8, calculate the total package weight dispatched using SUM(C2:C5).
4
In cell B9, calculate the grand total shipping cost using SUM(E2:E5).
Solution & Formula Breakdown
Let's review the lookup and calculation logic step by step:
1. Looking up Freight Rate with VLOOKUP or XLOOKUP (D2:D5)
=VLOOKUP(B2, $G$2:$I$4, 3, FALSE)
Using VLOOKUP:
B2 is the lookup value (e.g. "Z-02").$G$2:$I$4 is the reference table range (locked with dollar signs $ so it stays fixed when dragging down).3 indicates the 3rd column (Rate / kg).FALSE requests an exact match.
Alternative modern syntax: =XLOOKUP(B2, $G$2:$G$4, $I$2:$I$4).
2. Calculating Total Shipping Cost (E2:E5)
=C2 * D2
Multiply package weight in Column C by the rate per kilogram in Column D (e.g. 5 * 4 = 20 for ORD-101).
3. Aggregating Total Weight (Cell B8)
=SUM(C2:C5)
Sums all individual package weights in the dispatch batch.
4. Calculating Grand Total Shipping Invoices (Cell B9)
=SUM(E2:E5)
Sums total line item shipping amounts across orders in Column E.