Lookup Functions
Advanced
Capstone: price a quote from the rate card
Two lookups, and one SKU that is not in the list.
Task:
Sales sent three SKUs to price. In column F return the price for each from the rate card, showing Not listed when a SKU is not there. In column G return the product name for the two that do exist.
Learning Objectives:
- Handle a missing lookup value
- Anchor a lookup table with $
- Build INDEX/MATCH from its two halves
Hints
Solve without hints for +5 XP
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 | Rate card | Quote | |||||
| 2 | SKU | Product | Price | SKU | Price | Product | |
| 3 | SKU-100 | Chair | 149 | SKU-220 | |||
| 4 | SKU-150 | Desk | 299 | SKU-100 | |||
| 5 | SKU-220 | Lamp | 79 | SKU-999 | |||
| 6 | SKU-300 | Shelf | 120 |
What this exercise teaches (contains the answer)
XLOOKUP has a not-found argument built in, which is why it has largely replaced VLOOKUP wrapped in IFERROR. INDEX/MATCH is here to show the other half of the idea: MATCH answers where, INDEX answers what, and separating the two is what lets you look leftwards — something VLOOKUP cannot do at all.