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:
Note that you will need to click and drag this formula to the right until you encounter #NUM! in one of the cells.
This indicates that you have successfully returned each value that corresponds to the value that you looked up.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: How to Use VLOOKUP to Return All Matches
Excel: Use VLOOKUP to Return Blank Instead of Zero
Excel: How to Use TRUE or FALSE in VLOOKUP
