Table of Contents
VLOOKUP is a useful function in Excel that allows users to search for a specific value in a table and return a corresponding value from a different column. However, by default, it can only return a single value vertically. To return multiple values horizontally, users can use a combination of INDEX, MATCH, and TRANSPOSE functions. This will allow the user to specify the column containing the desired values and transpose the results horizontally. This method can be useful for organizing and analyzing data in a more efficient manner.
Excel: Use VLOOKUP to Return Multiple Values Horizontally
Often you may want to use the VLOOKUP function in Excel to return multiple values horizontally.
Unfortunately this isn’t possible using the VLOOKUP function but it is possible by using the following formula with the INDEX, SMALL and IF functions:
=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 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 drag this formula horizontally to display multiple values.
The following example shows how to use this formula in practice.
Example: Use VLOOKUP 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)))
We can then click and drag this formula horizontally to the right 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.
Notice that each of these values represents a value in the points column that corresponds to the “Mavs” in the team column:
This indicates that you have successfully returned each value that corresponds to the value that you looked up.
The following tutorials explain how to perform other common tasks in Excel:
Cite this article
stats writer (2024). How can I use VLOOKUP to return multiple values horizontally in Excel?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-use-vlookup-to-return-multiple-values-horizontally-in-excel/
stats writer. "How can I use VLOOKUP to return multiple values horizontally in Excel?." PSYCHOLOGICAL SCALES, 23 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-use-vlookup-to-return-multiple-values-horizontally-in-excel/.
stats writer. "How can I use VLOOKUP to return multiple values horizontally in Excel?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-use-vlookup-to-return-multiple-values-horizontally-in-excel/.
stats writer (2024) 'How can I use VLOOKUP to return multiple values horizontally in Excel?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-use-vlookup-to-return-multiple-values-horizontally-in-excel/.
[1] stats writer, "How can I use VLOOKUP to return multiple values horizontally in Excel?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I use VLOOKUP to return multiple values horizontally in Excel?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.