Look up a placement fee from a category-by-level fee grid
MATCH finds where a value sits in a list and hands back a position, not the value itself — INDEX is what turns that row position and a second one, from a matching MATCH on the other axis, into the one cell where they cross.
Task:
You handle billing for a staffing agency that places candidates into four job categories, each with its own fee for Entry, Mid, and Senior level placements — laid out as a small grid above. Four placements closed this week. In D8, work out the fee for each one by finding the row for its category and the column for its level in the grid, then copy down to D11. In D12, total the week's fees.
Learning Objectives:
- Recognize when a lookup needs two axes and a single VLOOKUP or HLOOKUP can't cover both
- Use MATCH to turn a lookup value into a row or column position rather than a value
- Combine two MATCH results in one INDEX to read the cell where a row and a column cross
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 | Entry | Mid | Senior | |
| 2 | Administrative | 800 | 1200 | 1800 |
| 3 | Warehouse | 600 | 900 | 1400 |
| 4 | Accounting | 1000 | 1600 | 2400 |
| 5 | IT Support | 1100 | 1700 | 2600 |
| 6 | ||||
| 7 | Candidate | Category | Level | Placement Fee |
| 8 | Maria Chen | Warehouse | Mid | |
| 9 | Tom Alvarez | Accounting | Senior | |
| 10 | Priya Nair | IT Support | Entry | |
| 11 | Diego Ruiz | Administrative | Senior | |
| 12 | Total fees |
What this exercise teaches (contains the answer)
MATCH(B8,$A$2:$A$5,0) doesn't look up Warehouse's fee — it looks up where "Warehouse" sits in the category list, which is position 2. MATCH(C8,$B$1:$D$1,0) does the same for "Mid" against the level headers, position 2. Neither number means anything on its own; INDEX($B$2:$D$5,2,2) is what turns "row 2, column 2" into the one cell where Warehouse's row crosses Mid's column, 900. A single VLOOKUP could have found the right row for the category, but it has no way to also pick out the right column for the level — that second axis is exactly the gap the second MATCH fills, and it's why a two-way lookup takes two MATCHes feeding one INDEX rather than one lookup doing double duty. The dollar signs on the grid's range keep it pinned in place as the formula fills down through four different candidates.