One cell from a rep × quarter grid (INDEX MATCH twice)
Test case
1
Reset
Submit
A
B
C
D
E
F
G
1
2
3
4
5
6
7
8
9
10
○ Ready Enter formula in highlighted cells, then click Submit.
Test case
1
Reset
Submit
A
B
C
D
E
F
G
1
2
3
4
5
6
7
8
9
10
○ Ready Enter formula in highlighted cells, then click Submit.
The problem
Problem statement
The dashboard grid in B2:E5 is reps down the side and quarters across the top. The rep to show is H1 and the quarter is H2. Put the value in the Result cell H3.
Both labels must be looked up — adding a rep or a quarter must not require editing the formula.
Problem data
Show the sheet as a table (selectable, copyable)
| A | B | C | D | E | G | H | |
|---|---|---|---|---|---|---|---|
| 1 | Rep | Q1 | Q2 | Q3 | Q4 | Rep | Cleo |
| 2 | Ada | 120 | 150 | 180 | 200 | Quarter | Q3 |
| 3 | Ben | 90 | 110 | 140 | 160 | Result | |
| 4 | Cleo | 300 | 280 | 260 | 240 | ||
| 5 | Dana | 50 | 60 | 70 | 80 |
Notes
Two MATCH calls give INDEX a row and a column: one searches the rep labels, the other the quarter labels. This is the pattern behind every crosstab dashboard, and it keeps working when rows or columns are reordered.
How to approach
Your answer goes in H3.
- Target cell is H3
- INDEX over the body: B2:E5
- Row from MATCH(H1, A2:A5, 0)
- Shape: =INDEX(?, MATCH(?,?,?), MATCH(?,?,?))
Function guide
FAQ
How do I look up a value by row and column at once?
=INDEX(body, MATCH(row_label, row_labels, 0), MATCH(col_label, col_labels, 0)). Two MATCH calls, one INDEX.
Why is VLOOKUP off by one column here?
The header MATCH counts from the first quarter column, while VLOOKUP counts from the rep column. Add 1 to the MATCH result.