Download the example workbook
This tutorial will demonstrate how to perform a 2-Dimensional lookup with the INDEX and MATCH Functions in Excel and Google Sheets.
Match Two Axis – Row & Column (2D Lookup)
Let’s go into the details.
To find the row, use the MATCH Function with your first value.
This finds the row containing “Berlin” and returns its position within the given range. “Berlin” is the 1st city in Column B, so the MATCH Function returns 1.
Then we repeat for the column we are interested in.
“Vienna” is the 2nd city, so the MATCH Function returns 2.
Now that we have the positions for the row and column, we use them as arguments in the INDEX Function to return the value of the cell where they intersect (here, the distance from Berlin to Vienna).
Replacing the column and row numbers with the MATCH Functions that found them gives us our original formula:
INDEX MATCH MATCH in Google Sheets
These formulas work exactly the same in Google Sheets as in Excel.