Table of Contents
INDEX MATCH is a powerful function in Excel that allows users to retrieve data from a specific cell in a table based on certain criteria. One of its key advantages is its ability to return multiple values horizontally, which can be useful in various data analysis and reporting tasks. This is achieved by using the MATCH function to find the position of the desired value, and then using the INDEX function to retrieve that value from the corresponding row. By adjusting the column argument of the INDEX function, users can retrieve multiple values from the same row, creating a horizontal array of data. This feature of INDEX MATCH makes it a highly efficient and flexible tool for handling large datasets and generating custom reports.
Excel: Use INDEX MATCH to Return Multiple Values Horizontally
You can use the INDEX and MATCH functions in Excel to look up a value in one column and return the corresponding value from another column.
If you would like to use INDEX and MATCH to return multiple values horizontally, you can use the following formula:
=INDEX($B$2:$B$13, SMALL(IF($A$17=$A$2:$A$13,ROW($A$2:$A$13)-ROW($B$2)+1), COLUMN(A1)))
This particular formula looks up the value in cell A17 in the range A2:A13 and returns the corresponding value in the range B2:B13.
You can then drag this formula horizontally to display multiple values.
The following example shows how to use this formula in practice.
Example: Use INDEX MATCH to Return Multiple Values Horizontally
Suppose we have the following dataset in Excel that contains information about points scored by basketball players on various teams:
Suppose we would like to look up “Mavs” in the team column and return each points value horizontally.
We can type the following formula into cell B17 to do so:
=INDEX($B$2:$B$13, SMALL(IF($A$17=$A$2:$A$13,ROW($A$2:$A$13)-ROW($B$2)+1), COLUMN(A1)))
The following screenshot shows how to use this formula in practice:
We can then click and drag this formula horizontally to the right until we encounter a cell that shows #NUM! to display each of the points values that corresponds with the “Mavs” in the team column:
The formula correctly returns the values 24, 40, 15 and 25 in the same row.
Feel free to change the team name in cell A17 to look up the points values for a different team.
The following tutorials explain how to perform other common tasks in Excel:
Cite this article
stats writer (2024). How can INDEX MATCH be used to return multiple values horizontally in Excel?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-index-match-be-used-to-return-multiple-values-horizontally-in-excel/
stats writer. "How can INDEX MATCH be used to return multiple values horizontally in Excel?." PSYCHOLOGICAL SCALES, 22 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-index-match-be-used-to-return-multiple-values-horizontally-in-excel/.
stats writer. "How can INDEX MATCH be used to return multiple values horizontally in Excel?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-index-match-be-used-to-return-multiple-values-horizontally-in-excel/.
stats writer (2024) 'How can INDEX MATCH be used to return multiple values horizontally in Excel?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-index-match-be-used-to-return-multiple-values-horizontally-in-excel/.
[1] stats writer, "How can INDEX MATCH be used to return multiple values horizontally in Excel?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can INDEX MATCH be used to return multiple values horizontally in Excel?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.