Table of Contents
The INDEX MATCH function in Google Sheets allows users to retrieve multiple results by combining the INDEX and MATCH functions. This powerful formula enables users to search for a specific value within a range of data and return the corresponding value from a different column or row. By using INDEX MATCH, users can easily retrieve multiple results that meet their search criteria, providing a more efficient and accurate way of managing data in Google Sheets. This feature is especially useful when working with large datasets and performing complex data analysis.
Google Sheets: Use INDEX MATCH to Return Multiple Results
You can use the following syntax with INDEX MATCH in Google Sheets to return multiple results:
=IFERROR(INDEX($B$2:$B$11,SMALL(IF($D$2=$A$2:$A$11,ROW($A$2:$A$11)-ROW($A$2)+1),ROW(1:1))),"")
This particular formula returns all of the values in the range B2:B11 where the corresponding value in the range A2:A11 is equal to the value in cell D2.
The following example shows how to use this formula in practice.
Example: Use INDEX MATCH to Return Multiple Results in Google Sheets
Suppose we have the following dataset in Google Sheets that shows the team and player name of various basketball players:
Suppose that we would like to return the names of each player on the Mavs team.
To do so, we can specify “Mavs” in cell D2 and then type the following formula into cell E2:
=IFERROR(INDEX($B$2:$B$11,SMALL(IF($D$2=$A$2:$A$11,ROW($A$2:$A$11)-ROW($A$2)+1),ROW(1:1))),"")
Once we press Enter, the name of the first player on the Mavs team will be returned:
We can then drag and fill this formula down to the remaining cells in column E to display the names of each player on the Mavs team:
Notice that the names of each of the four players on the Mavs team are now shown.
Note that if you change the name of the team in cell D2, the names of the players shown in column E will change accordingly:
Additional Resources
The following tutorials explain how to perform other common tasks in Google Sheets: