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.
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.