Excel track · Exercise 47 of 86 Paid
Two-way INDEX MATCH
Read one cell out of a grid by matching its row and column labels.
Intermediate
Three regions, three quarters
Read-only data · answer in B7
In B7, read the units for the region in B5 and the quarter in B6 from the grid B2:D4.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Region | Q1 | Q2 | Q3 |
| 2 | North | 120 | 140 | 160 |
| 3 | South | 90 | 110 | 130 |
| 4 | West | 75 | 95 | 115 |
| 5 | Region? | South | ||
| 6 | Quarter? | Q3 | ||
| 7 | Units |
Reference answer
What it does
Two MATCH calls answer two separate questions. South is second in A2:A4, so the first returns 2. Q3 is third in the header row B1:D1, so the second returns 3. INDEX reads B2:D4 at row 2, column 3, and B7 shows 130.
The two lists run in different directions, which is why they are separate ranges. A2:A4 is a column of regions and B1:D1 is a row of quarters. Give the quarter MATCH the region column and Q3 is not among the labels it searches, so the cell shows #N/A.
Both calls need the zero, since neither list is sorted. A wrong number from either one still produces a figure: swap the two MATCH calls and B7 reads 95, West's Q2. When a two-way answer is a number you did not expect, check each MATCH on its own before blaming INDEX.
Syntax
=INDEX(grid, row, column)Worked example
On Three regions, three quarters, =INDEX(B2:D4,MATCH(B5,A2:A4,0),MATCH(B6,B1:D1,0)) returns 130.
=INDEX(B2:D4,MATCH(B5,A2:A4,0),MATCH(B6,B1:D1,0))Common errors
| Error | What it means and how to fix it |
|---|---|
#N/A | A region missing from A2:A4, or a quarter missing from B1:D1, leaves one of the two positions without a value. |
Swapped positions | The two MATCH calls went in the wrong order, so INDEX reads row 3 column 2 and returns 95. |
#REF! | A MATCH run over a range that reaches past the grid returns a position B2:D4 does not have. |
At work
A budget grid with regions down the side and quarters across the top, read by label each month.