Table of Contents
To create a list in Google Sheets based on specific criteria, follow these steps:
1. Open Google Sheets and create a new spreadsheet.
2. In the first row, create column headings for the criteria you want to use.
3. Enter your data in the subsequent rows, making sure each entry corresponds to the appropriate column heading.
4. Click on the “Data” tab and select “Filter” from the drop-down menu.
5. A small arrow will appear next to each column heading. Click on the arrow for the column you want to filter.
6. In the drop-down menu, select “Filter by condition” and choose the specific criteria you want to use.
7. Only the entries that meet the selected criteria will be displayed.
8. To create a list of these filtered entries, select all the visible cells and copy them.
9. Paste the copied cells in a new location to create a separate list based on the specific criteria.
10. You can also use the “Sort” function under the “Data” tab to further organize your list based on a specific column.
Google Sheets: Create a List Based on Criteria
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: