# Excel: Return Multiple Values Based on Single Criteria

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