Table of Contents
Google Sheets has the ability to return multiple values based on a single criteria through the use of functions such as FILTER, QUERY, and VLOOKUP. These functions allow users to specify a criteria, such as a certain value or condition, and then retrieve all matching data from a specified range of cells. This makes it possible to quickly and efficiently extract specific information from a large dataset, saving time and effort for users. By utilizing these functions, Google Sheets makes it easy to retrieve multiple values that meet a specific criteria, providing a powerful tool for data analysis and manipulation.
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:
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: