Look up a freight rate keyed on two fields at once by concatenating them
XLOOKUP only ever matches against one array, so a rate card keyed on carrier and zone together needs its lookup value built the same way the rate card's key is: joined with &.
Task:
You coordinate outbound freight for a distribution center. The rate card prices every carrier-and-zone combination, but its key column stores both as one piece of text, carrier and zone joined with a hyphen, because XLOOKUP can only match a single column, not two at once. Each shipment in the queue lists its carrier and zone separately. In D2, join B2 and C2 the same way the rate card does and use XLOOKUP to pull the matching rate per lb — and for a carrier the rate card doesn't carry at all, show "Not quoted" instead of an error. Copy down to row 5.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve 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.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Shipment | Carrier | Zone | Rate per lb ($) | Rate Card Key | Rate per lb ($) | |
| 2 | SH-401 | FedEx | Zone 2 | FedEx-Zone 1 | 4.2 | ||
| 3 | SH-402 | UPS | Zone 3 | FedEx-Zone 2 | 5.1 | ||
| 4 | SH-403 | FedEx | Zone 1 | FedEx-Zone 3 | 6.35 | ||
| 5 | SH-404 | DHL | Zone 2 | UPS-Zone 1 | 4 | ||
| 6 | UPS-Zone 2 | 4.85 | |||||
| 7 | UPS-Zone 3 | 5.6 |
What this exercise teachesMay contain the answer
XLOOKUP compares one lookup value against one array, so a rate card priced on two fields together, carrier and zone, has nowhere to put that second field unless the key column already combines them — which is exactly what F2:F7 does. B2&"-"&C2 rebuilds that same combination on the shipment side, turning FedEx and Zone 2 into the text "FedEx-Zone 2" so it has something to match against row by row rather than cell by cell. DHL never appears anywhere in the rate card, not under any zone, so no amount of correct concatenation produces a match for it — that is what the fourth argument is for, returning "Not quoted" in its place rather than the #N/A a reader would have to go investigate. The two FedEx shipments land on different rates, 4.20 for Zone 1 against 5.10 for Zone 2, which is the whole reason the key needed both fields in the first place: carrier alone was never enough to tell those two rows apart.