Table of Contents
The INDEX and MATCH functions in Excel can be used to return multiple values vertically by combining their capabilities. The INDEX function allows you to specify a range of cells and return a specific cell or cells within that range. The MATCH function allows you to find the position of a value within a range of cells. By using these functions together, you can create a formula that will return multiple values vertically from a specified range of cells. This can be useful when working with large data sets and needing to extract specific information.
Excel: Use INDEX and MATCH to Return Multiple Values Vertically
You can use the following basic formula with INDEX and MATCH to return multiple values vertically in Excel:
=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 and MATCH to Return Multiple Values Vertically
Suppose we have the following dataset in Excel that shows the name and team of various basketball players:
Now suppose we would like to return the names of each player who is on the Mavs team.
To do so, we can 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:
The following tutorials explain how to perform other common tasks in Excel:
Cite this article
stats writer (2024). How can I use the INDEX and MATCH functions in Excel to return multiple values vertically?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-use-the-index-and-match-functions-in-excel-to-return-multiple-values-vertically/
stats writer. "How can I use the INDEX and MATCH functions in Excel to return multiple values vertically?." PSYCHOLOGICAL SCALES, 27 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-use-the-index-and-match-functions-in-excel-to-return-multiple-values-vertically/.
stats writer. "How can I use the INDEX and MATCH functions in Excel to return multiple values vertically?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-use-the-index-and-match-functions-in-excel-to-return-multiple-values-vertically/.
stats writer (2024) 'How can I use the INDEX and MATCH functions in Excel to return multiple values vertically?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-use-the-index-and-match-functions-in-excel-to-return-multiple-values-vertically/.
[1] stats writer, "How can I use the INDEX and MATCH functions in Excel to return multiple values vertically?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I use the INDEX and MATCH functions in Excel to return multiple values vertically?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.