Table of Contents
Google Sheets is an incredibly powerful and versatile tool for creating and managing data. From creating simple lists to complex data models, Google Sheets can be used to do almost anything. One of the most useful features of Google Sheets is the ability to create a list based on criteria. With this feature, you can easily sort and filter data to find exactly what you need in a fraction of the time. This can be especially useful when dealing with large datasets or when you need to quickly create a report. With Google Sheets, you can easily create lists based on criteria and save time and effort while keeping your data organized.
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.
In conclusion, this tutorial has demonstrated how to use the IFERROR and INDEX functions in Google Sheets to create a list based on criteria. Examples have been provided to show how to use the formula to create a list based on one criteria and multiple criteria. This is a useful technique that can be used in many different scenarios. With the help of this tutorial, you should now be able to create a list based on criteria in Google Sheets.