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