Course outline / Lookup functions
Lesson 28 of 75 · Advanced · 3 exercises
Two-way lookups
Find the value where a row and a column meet, with INDEX and two MATCHes.
What you'll practise
- INDEX with a row and a column
- Find the row with MATCH
- Put them together
INDEX with a row and a column
Given a grid, INDEX can take a row number and a column number, and return the cell where they cross.
=INDEX(B2:D4, 2, 3) is the 2nd row, 3rd column of the grid.
In cell F4, return the value in row 3, column 2 of B2:D4.
Find the row with MATCH
In cell F4, use MATCH to find which row of A2:A4 holds the region typed in F2.
Put them together
Now let MATCH find both numbers: one MATCH for the row and one for the column.
=INDEX(grid, MATCH(row_value, row_labels, 0), MATCH(col_value, col_labels, 0))
In cell F4, return the sales for the region in F2 and the month in G2, from B2:D4.
Try it in a real spreadsheet
Every step checks your answer instantly and tells you exactly what to fix.