Excel: Use INDEX and MATCH to Return Multiple Values Vertically


You can use the following basic formula with INDEX and MATCH to return multiple values vertically in Excel:

=IFERROR(INDEX($B$2:$B$11,SMALL(IF($D$2=$A$2:$A$11,ROW($A$2:$A$11)-ROW($A$2)+1),ROW(1:1))),"")

This particular formula returns all of the values in the range B2:B11 where the corresponding value in the range A2:A11 is equal to the value in cell D2.

The following example shows how to use this formula in practice.

Example: Use INDEX and MATCH to Return Multiple Values Vertically

Suppose we have the following dataset in Excel that shows the name and team of various basketball players:

Now suppose we would like to return the names of each player who is on the Mavs team.

To do so, we can type the following formula into cell E2:

=IFERROR(INDEX($B$2:$B$11,SMALL(IF($D$2=$A$2:$A$11,ROW($A$2:$A$11)-ROW($A$2)+1),ROW(1:1))),"")

Once we press Enter, the name of the first player on the Mavs team will be returned:

We can then drag and fill this formula down to the remaining cells in column E to display the names of each player on the Mavs team:

INDEX and MATCH return multiple values in Excel vertically

Notice that the names of each of the four players on the Mavs team are now shown.

Note that if you change the name of the team in cell D2, the names of the players shown in column E will change accordingly:

The names of each of the three players on the Nets team are now shown.

Additional Resources

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

Excel: How to Perform a VLOOKUP with Two Lookup Values
Excel: How to Use VLOOKUP to Return Multiple Columns
Excel: How to Use VLOOKUP to Return All Matches

5 Replies to “Excel: Use INDEX and MATCH to Return Multiple Values Vertically”

  1. It works but I only get the first value. When I drag the formula down, or to the right, it either doesn’t put a value in the cell or it puts the same value in the cell. How do I apply this formula so that I can list the multiple returned values across a set of columns (horizontal)? thanks

  2. Hi Zach I did a INDEX with a MATCH formula – the challenge I have is that it does not include all the unique values it only repeats the first item that meets the criteria. Can you provide some guidance on how I can return all values?

    I have 2 sheets one containing all the data the second is the sheet where I want to automatically sort the one column’s data from sheet 1 different columns and rows depending on the stream (group) and grade of a position.

    Unfortunately I can’t upload a sample for you to see my challenge.

    Hoping you can assist – have a great day!

  3. Thanks! This was very helpful. However I was trying to do this with a dataset where the value that was being looked up was a numeral and it didn’t work. Any tips to get it working in this case?

  4. Hello,

    Thank you for the awesome tutorial, certainly assisted me in my use case!
    However, I would like to ask how you would edit the formula if I have player names on columns C, D, and E as well.

Leave a Reply

Your email address will not be published. Required fields are marked *