Two-way lookup: rep and quarter at once (INDEX MATCH)
The problem
Problem statement
A sales dashboard asks for one number: the result of the rep in F1 in the quarter in F2. The grid is B2:D4 with rep labels in A2:A4 and quarter labels in B1:D1. Put the value in the Result cell F3 — one formula that reads both labels, not a different formula per cell.
Both labels must be looked up by formula; changing F1 or F2 has to change the answer with no edit to the formula.
Problem data
Show the sheet as a table (selectable, copyable)
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Rep | Q1 | Q2 | Q3 | Rep | Ben |
| 2 | Ada | 120 | 150 | 180 | Quarter | Q3 |
| 3 | Ben | 90 | 110 | 140 | Result | |
| 4 | Cleo | 200 | 180 | 210 |
Notes
Two MATCH calls stacked inside INDEX: the first finds the row inside the rep labels, the second finds the column inside the quarter labels, and INDEX takes row and column positions. This is the pattern for any crosstab, and it survives rows or columns being reordered.
How to approach
Your answer goes in F3. On submit we re-run your formula against 3 different sets of input, so a hardcoded number will fail — the formula has to follow the cells.
- Target cell is F3
- INDEX's first argument is the body of the grid: B2:D4
- Row comes from MATCH(F1, A2:A4, 0); column from MATCH(F2, B1:D1, 0)
- Shape: =INDEX(?, MATCH(?,?,?), MATCH(?,?,?))
Official solutions
Show 2 solutions — try it yourself first
Approach 1: INDEX with two MATCH calls
=INDEX(B2:D4, MATCH(F1, A2:A4, 0), MATCH(F2, B1:D1, 0))
MATCH("Ben", A2:A4, 0) is 2 and MATCH("Q3", B1:D1, 0) is 3, so INDEX returns row 2 column 3 of B2:D4 — 140.
Approach 2: VLOOKUP with MATCH as the column index
=VLOOKUP(F1, A2:D4, MATCH(F2, B1:D1, 0) + 1, FALSE)
MATCH turns the quarter label into a position inside B1:D1 (3), and +1 shifts it into VLOOKUP's column count, which starts at A. Same answer as INDEX MATCH, but it only works while the row key stays in the leftmost column — and the +1 is the part people forget.
Function guide
FAQ
How do I look up a value by row and column at the same time?
=INDEX(body, MATCH(row_label, row_labels, 0), MATCH(column_label, column_labels, 0)). Two MATCH calls, one INDEX.
Why not just use VLOOKUP here?
VLOOKUP can do it if you give it the column number, which is why MATCH(F2, header_row, 0) works as its third argument. But VLOOKUP still needs the row key in the leftmost column, so INDEX MATCH generalises further.