Google Sheets: Return Multiple Values Based on Single Criteria


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

=INDEX($A$1:$A$14, SMALL(IF(D$2=$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 D2.

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

Example: Return Multiple Values Based on Single Criteria in Google Sheets

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

Suppose we would like to return every year that the Warriors won the title.

To do so, type “Warriors” into cell D2 and then type the following formula into cell E2:

=INDEX($A$1:$A$14, SMALL(IF(D$2=$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:

Google Sheets return multiple values based on single criteria

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 D2, the list of years will automatically update.

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

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

  • 2010
  • 2020

Feel free to change the team name in cell D2 to any team name you’d like.

Additional Resources

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

Google Sheets: Count Column if Another Column Meets Criteria
Google Sheets: Pull Data from Another Sheet Based on Criteria
Google Sheets: How to Create a List Based on Criteria

Leave a Reply

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