A two-way lookup with INDEX and two MATCHes
PremiumIn H3, find the sales for the product in H1 and the region in H2. INDEX the numbers B2:E5 with one MATCH for the row and one for the column: INDEX(B2:E5,MATCH(H1,A2:A5,0),MATCH(H2,B1:E1,0)).
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Product | North | South | East | West | Product | Bags | ||
| 2 | Shoes | 420 | 380 | 290 | 510 | Region | East | ||
| 3 | Shirts | 310 | 260 | 340 | 220 | Sales | |||
| 4 | Bags | 150 | 210 | 180 | 270 | ||||
| 5 | Hats | 90 | 120 | 60 | 140 | ||||
| 6 | |||||||||
| 7 | |||||||||
| 8 |
Premium lesson
Unlock INDEX and MATCH and every other lesson
This is a premium lesson. Premium lets you solve it and every other lesson, take the timed mock tests, and earn certificates you can share on LinkedIn.
It comes up in this mock test: Excel test for job interviews
Why this works
INDEX(B2:E5,row,column) returns the value where a row and a column of the block cross. MATCH(H1,A2:A5,0) finds Bags in position 3, and MATCH(H2,B1:E1,0) finds East in position 3, so INDEX returns the third row and the third column: 180. Change H1 or H2 and the answer follows.
Common mistake: Starting the MATCH ranges at row 1 or column A. The positions are then one off from the block INDEX reads.
The answer
- H3
=INDEX(1B2:E5,2MATCH(H1,A2:A5,0),3MATCH(H2,B1:E1,0))
In plain words: In 1the sales in B2:E5, return the cell on 2the row for the product in H1 and in 3the column for the region in H2.