How can I use Google Sheets to return multiple values based on a single criteria?

By /

Google Sheets is a powerful tool that allows users to organize and analyze data in a spreadsheet format. One of its useful features is the ability to return multiple values based on a single criteria. This means that you can easily filter and retrieve specific data from a larger dataset using a certain condition or criteria. By using functions such as FILTER, VLOOKUP, and QUERY, you can customize your search and obtain a list of values that meet your desired criteria. This feature is particularly helpful for data analysis and making informed decisions based on specific data points. With its user-friendly interface and various functions, Google Sheets provides an efficient solution for retrieving multiple values based on a single criteria.

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.

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:

x

Related terms:

  1. How can Google Sheets return multiple values based on a single criteria?
  2. How to return multiple values based on single criteria in Excel?
  3. How can I count unique values in Google Sheets based on multiple criteria?
  4. Excel: How to Count Unique Values Based on Multiple Criteria
  5. Google Sheets: Create a List Based on Criteria
  6. What is the earliest date in Google Sheets based on specific criteria?
  7. How do I use VLOOKUP with Multiple Criteria in Google Sheets?
  8. How to use Google Sheets: INDEX MATCH with Multiple Criteria
  9. How can I count unique data in Google Sheets using multiple criteria?
  10. Can I use VLOOKUP to return multiple columns in Google Sheets?