Chapter 09 · 8 min · Basic
Bringing in the other sheet
The question is how much did we sell in each city, and the orders file has no city column. It has a customer id. The cities live in somebody else's list.
So you bring the two together. This is the most ordinary thing an analyst does, and it has two failure modes that both end with a report that looks finished: rows that match the wrong copy, and rows that match nothing and vanish from the total.
Neither one raises an error. Both are cheap to check first, and almost impossible to spot afterwards.
The grid
Two blocks on one grid. Orders in A1:C11 — order_id, customer_id, amount_myr. The customer list in E1:G6 — customer_id, name, city. The engine here has one sheet, so the second block sits beside the first rather than on its own tab; everything works the same way. Work in column I.
Pulling the city across
XLOOKUP is the bridge: take the customer id off the order row, find it in the customer list's id column, bring back the matching city.
Row 2 belongs to C-001, the list says Penang, and Penang is what arrives.
That is the whole mechanic, and it is genuinely this simple when both sides behave. The rest of this chapter is about the two ways they do not — and the reason to learn them here rather than later is that the formula looks exactly the same in both cases.
Order row 2 belongs to customer C-001, who the list says is in Penang. What comes back?
Scroll to see all 9 columns →
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | order_id | customer_id | amount_myr | customer_id | name | city | |||
| 2 | 7001 | C-001 | 41.00 | C-001 | Aisyah | Penang | =XLOOKUP(B2,$E$2:$E$6,$G$2:$G$6) | ||
| 3 | 7002 | C-002 | 62.50 | C-002 | Wei Ming | Kuala Lumpur | |||
| 4 | 7003 | C-003 | 128.75 | C-003 | Priya | Johor Bahru | |||
| 5 | 7004 | C-001 | 33.20 | C-005 | Hafiz | Penang | |||
| 6 | 7005 | C-004 | 87.50 | C-002 | Wei Ming | Ipoh | |||
| 7 | 7006 | C-002 | 71.15 | ||||||
| 8 | 7007 | C-003 | 96.40 | ||||||
| 9 | 7008 | C-005 | 27.00 | ||||||
| 10 | 7009 | C-001 | 48.00 | ||||||
| 11 | 7010 | C-005 | 19.90 |
Basic
The rest of this chapter is Basic
The Excel tutorial and Spreadsheet Fundamentals are free here, forever. This track is the paid half: what to actually do with a spreadsheet once somebody sends you one, from the first look to a summary you can defend.
RM 25/mo · cancel anytime
Still to come in this chapter
- 02One answer, even when there are two
- 03Check the other side is unique first
- 04The orders that match nobody
- 05Count the money before you bring anything across
- 06The order of operations