You can use the following basic formula to return multiple values in Excel based on a single criteria:

=INDEX($A$1:$A$14, SMALL(IF(E$1=$B$1:$B$14, MATCH(ROW($B$1:$B$14), ROW($B$1:$B$14)), ""), ROWS($A$1:A1)))

This particular formula returns all of the values in the range **A1:A14** where the corresponding value in the range **B1:B14** is equal to the value in cell **E1**.

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

**Example: Return Multiple Values Based on Single Criteria in Excel**

Suppose we have the following dataset that shows the winner of the NBA finals during various years:

We can type the following formula into cell **E2** to return every year that the Warriors were the winners:

=INDEX($A$1:$A$14, SMALL(IF(E$1=$B$1:$B$14, MATCH(ROW($B$1:$B$14), ROW($B$1:$B$14)), ""), ROWS($A$1:A1)))

Once we press **Enter**, the first year that the Warriors won will be shown:

We can then drag and fill this formula down to other cells in column E until we encounter a #NUM! value:

We can see that the Warriors won the finals during the following years:

- 2015
- 2017
- 2018
- 2022

If we change the team name in cell **E1**, the list of years will automatically update.

For example, suppose we type “Lakers” in cell **E1** instead:

We can see that the Lakers won the finals during the following years:

- 2010
- 2020

**Additional Resources**

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

Excel: How to Extract Unique Values Based on Criteria

Excel: How to Randomly Select Cells Based on Criteria

Excel: How to Find Top 10 Values Based on Criteria