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

 

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

Leave a Reply

Your email address will not be published.