Table of Contents
The INDEX MATCH function in Excel is a powerful tool that allows users to search across multiple columns in a spreadsheet. This function combines the use of the INDEX function, which returns a cell value based on a specified row and column number, and the MATCH function, which searches for a specified value within a range of cells. By using the INDEX MATCH function, users can easily locate and retrieve data from multiple columns, making data management and analysis more efficient. This function is particularly useful when dealing with large amounts of data or when data is organized in a non-linear manner. In summary, the INDEX MATCH function is a versatile tool that enables users to search and retrieve data from multiple columns in Excel with ease.
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:
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.
Additional Resources
The following tutorials explain how to perform other common operations in Excel: