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.

Notice that each of these values represents a value in the points column that corresponds to the “Mavs” in the team column:

Feel free to change the team name in cell A17 to look up the points values for a different team.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

Excel: How to Use IF Function with INDEX MATCH
Excel: How to Use SUM with INDEX MATCH
Excel: Use INDEX and MATCH to Return Multiple Values Vertically