INDEX MATCH MATCH – Excel & Google Sheets

This tutorial will demonstrate how to perform a 2D lookup with the INDEX and MATCH Functions in Excel and Google Sheets.

index match match 2d lookup Main Function

Match Two Axis –  Row & Column (2D Lookup)

The INDEX Function returns a value from a given Row and Column reference. We can supply both the row and column references with MATCH functions to perform a 2d-lookup:

index match match 2d lookup 01

 

Let’s go into the details.

MATCH Function

To find the row, use the MATCH Function with your first value.

index match match 2d lookup 02

The example above, 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.

index match match 2d lookup 02 01

“Vienna” is the 2nd city, so the MATCH Function returns 2.

AutoMacro - VBA Code Generator

INDEX Function

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

index match match 2d lookup 03

Replacing the column and row numbers with the MATCH Functions that found them gives us our original formula:

index match match 2d lookup 01

INDEX MATCH MATCH in Google Sheets

These formulas work exactly the same in Google Sheets as in Excel.

index match match 2d lookup Google Function

 

 

Excel Practice Worksheet

Practice Excel functions and formulas with our 100% free practice worksheets!

  • Automatically Graded Exercises
  • Learn Excel, Inside Excel!