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.
Learning Objectives:
- Supply XLOOKUP's fourth argument to hand back a chosen fallback instead of an error
- Run two lookups against the same match to pull back two different columns
- Lock a lookup table's range with absolute references so it survives being filled down
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 | 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 teaches (contains 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.