Look up carrier rates without an error for the one that isn't contracted
XLOOKUP hands back a fallback of your choosing instead of an error when nothing matches.
Task:
You handle logistics for an import/export company. This week's outbound orders are listed with just a carrier code; the full carrier names and negotiated rates per kg live in your contracted-rates table to the right. Fill in the carrier name (column C) and rate per kg (column D) for each order by looking them up — and for the one code that isn't a contracted carrier yet, show "Unlisted" instead of an error.
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 | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Order ID | Carrier Code | Carrier Name | Rate per kg ($) | Code | Carrier | Rate per kg ($) | |
| 2 | ORD-4471 | FEX | FEX | FastEx | 4.25 | |||
| 3 | ORD-4472 | UPX | UPX | UniPost | 3.9 | |||
| 4 | ORD-4473 | GDS | GDS | GoldStar Shipping | 5.1 | |||
| 5 | ORD-4474 | RLX | DHW | DH Worldwide | 4.75 | |||
| 6 | ORD-4475 | FEX |
What this exercise teachesMay contain the answer
XLOOKUP matches each order's code against F2:F5 and, when it finds one, returns the value sitting in the same position in whichever array you pointed it at — G for the name, H for the rate — which is why two XLOOKUPs per row are needed rather than one: each is reading a different result column off the same match. RLX never appears in F2:F5, and that's what the fourth argument is for: rather than VLOOKUP's #N/A, XLOOKUP returns "Unlisted" directly, so the sheet states plainly that this carrier isn't contracted rather than displaying an error a reader has to interpret. The absolute references matter because the rates table sits in the same five rows for every order — without them, filling down would slide the lookup range along with the formula and start comparing FEX against a table that no longer starts at row 2.