Excel: Use INDEX MATCH Across Multiple Columns

You can use the following syntax to use the INDEX and MATCH functions across multiple columns in Excel:

=INDEX($A$2:$A$5,MATCH(1,MMULT(--($B$2:$D$5=F2),TRANSPOSE(COLUMN($B$2:$D$5)^0)),0))

This particular formula will look up the value in cell F2 in the range B2:D5 and then return the corresponding value in the range A2:A5.

The following example shows how to use this syntax in practice.

Example: How to Use INDEX MATCH Across Multiple Columns in Excel

Suppose we have the following dataset in Excel that shows the names of basketball players in various positions on four different teams:

Now suppose we create another column that contains the name of each player:

Now suppose we would like to look up the name of each player and return the name of their team.

We can type the following formula into cell G2 to do so:

=INDEX($A$2:$A$5,MATCH(1,MMULT(--($B$2:$D$5=F2),TRANSPOSE(COLUMN($B$2:$D$5)^0)),0))

We can then click and drag this formula down to each remaining cell in column G:

Excel index match across multiple columns

Column G returns the name of the team that corresponds to each player name in column F.

For example:

And so on.

The following tutorials explain how to perform other common operations in Excel: