Look up a part when the code is not in the first column
The lookup VLOOKUP cannot do, because the answer sits to the left of the key.
Task:
A customer rings the parts counter and reads out code BLT-330. The price list is sorted by part name, so the codes sit in column B. Put the part name in B9 and the unit price in B10.
Learning Objectives:
- Find a row number with MATCH and an exact match
- Read a value off that row with INDEX
- Look up a value to the left of the key, which VLOOKUP cannot do
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 | |
|---|---|---|---|---|
| 1 | Part | Code | Supplier | Unit price |
| 2 | Brake pad set | BRK-114 | Fenwick | 38.5 |
| 3 | Oil filter | FLT-207 | Hollis | 12.75 |
| 4 | Timing belt | BLT-330 | Fenwick | 64.9 |
| 5 | Spark plug | PLG-052 | Ardent | 6.4 |
| 6 | Wiper blade | WPR-418 | Hollis | 15.25 |
| 7 | ||||
| 8 | Code asked for | BLT-330 | ||
| 9 | Part name | |||
| 10 | Unit price |
What this exercise teaches (contains the answer)
VLOOKUP can only return a column to the right of the one it searches, and here the part name sits to the left of the code, so it has nothing to offer. Splitting the problem removes the constraint: MATCH answers "which row?" and INDEX answers "what is in that row of this column?", and neither cares which side of the other they sit on. The same MATCH drives both answers, which is why changing the code in B8 updates them together.