Table of Contents
INDEX MATCH can be used to look up information across multiple columns in Excel. By creating an array of multiple columns, you can use the INDEX function to reference the columns in the array, and the MATCH function to find the relevant row. This allows you to return a value from any of the columns in the array, without having to manually copy and paste the data into another column. This can be especially useful if the data is constantly changing and needs to be updated regularly.
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:
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.