Two-way lookup with VLOOKUP and MATCH
VLOOKUP finds the row; MATCH tells it which column.
Task:
The staffing table has one row per site and one column per shift. In C10:C12 return the headcount for each site and shift requested, using VLOOKUP with MATCH for the column.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
Variations of INDEX MATCH
0/3The same formula in the other shapes it takes at work.
- Two-Way Lookup
Perform a two-way lookup to find values at the intersection of a row and column.
CoreAdvanced - Two-way lookup with VLOOKUP and MATCH
VLOOKUP finds the row; MATCH tells it which column.
Advanced - Two-way lookup: an approximate row and an exact column
Weight band down the side, zone across the top — two different kinds of match.
Advanced
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 | Site | Early | Late | Night |
| 2 | Leeds | 14 | 11 | 6 |
| 3 | Hull | 9 | 8 | 4 |
| 4 | York | 12 | 10 | 5 |
| 5 | Selby | 7 | 6 | 3 |
| 6 | ||||
| 7 | ||||
| 8 | ||||
| 9 | Site | Shift | Headcount | |
| 10 | Hull | Night | ||
| 11 | Leeds | Late | ||
| 12 | Selby | Early |
What this exercise teachesMay contain the answer
Matching against the whole header row, including the Site column, makes MATCH count columns exactly the way VLOOKUP does. Match against only B1:D1 and every result shifts one column left — the most common two-way lookup bug.