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:

  • The formula looks up Andy in the range B2:D5 and returns Mavs.
  • The formula looks up Bob in the range B2:D5 and returns Warriors.
  • The formula looks up Chad in the range B2:D5 and returns Kings.

And so on.

Additional Resources

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

Excel: How to Use INDEX and MATCH to Return Multiple Values Vertically
Excel: How to Use INDEX MATCH if Cell Contains Text
Excel: How to Use INDEX MATCH with 3 Criteria

Featured Posts

Leave a Reply

Your email address will not be published. Required fields are marked *