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