You can use the following basic formula to create a list based on criteria in Google Sheets:
=IFERROR(INDEX($A$2:$A$12,SMALL(IF($B$2:$B$12=$B$2,ROW($B$2:$B$12)),ROW(1:1))-1,1),"")
This particular formula creates a list of values in the range A2:A12 where the value in the range B2:B12 is equal to the value in cell B2.
The following examples show how to use this formula in practice with the following dataset in Google Sheets:
Example 1: Create List Based on One Criteria in Google Sheets
We can use the following formula to create a list of players who are on the Mavs team:
=IFERROR(INDEX($A$2:$A$12,SMALL(IF($B$2:$B$12=$B$2,ROW($B$2:$B$12)),ROW(1:1))-1,1),"")
We can type this formula into cell E2 and then drag it down to the remaining cells in column E to create a list of players who are on the Mavs team:
The result is a list of three players:
- Andy
- Bob
- Frank
We can look at the original dataset to confirm that all three of these players are on the Mavs team.
Example 2: Create List Based on Multiple Criteria in Google Sheets
We can use the following formula to create a list of players who are on the Mavs team and have a position of Guard:
=IFERROR(INDEX($A$2:$A$12,SMALL(IF(($B$2:$B$12=$B$2)*($C$2:$C$12=$C$2),ROW($B$2:$B$12)),ROW(1:1))-1,1),"")
We can type this formula into cell E2 and then drag it down to the remaining cells in column E to create a list of players who are on the Mavs team and have a position of Guard:
The result is a list of two players:
- Andy
- Frank
We can look at the original dataset to confirm that both of these players are on the Mavs team and have a position of Guard.
Additional Resources
The following tutorials explain how to perform other common tasks in Google Sheets:
Google Sheets: Pull Data from Another Sheet Based on Criteria
Google Sheets: Use VLOOKUP with Multiple Criteria
Google Sheets: Use INDEX MATCH with Multiple Criteria
