You can use the AND and OR operators to perform a Google Sheets query with multiple criteria.
Example of AND Operator:
=QUERY(A2:C10, "select A, B, C where A contains 'Hello' and C > 10")
Example of OR Operator:
=QUERY(A2:C10, "select A, B, C where A contains 'Hey' or C = 10")
The following examples show how to use each operator in practice.
Example 1: Google Sheets Query Using AND Operator
Suppose we have the following dataset in Google Sheets that contains information about various basketball teams:
We can use the following query to retrieve the teams that are in the East conference and scored more than 90 points:
=QUERY(A2:D11, "select A, B, C, D where B contains 'East' and C > 90")
The following screenshot shows how to use this query in practice:
Notice that the Magic and the Heat are selected because they’re the only two teams that meet both criteria specified in our query.
Example 2: Google Sheets Query Using OR Operator
Once again suppose we have the following dataset in Google Sheets:
We can use the following query to retrieve the teams that are in the East conference or scored more than 90 points:
=QUERY(A2:D11, "select A, B, C, D where B contains 'East' or C > 90")
The following screenshot shows how to use this query in practice:
Every team that is selected met at least one of the criteria specified in our query.
That is, each team selected was either in the ‘East’ conference or they scored more than 90 points.
Additional Resources
The following tutorials explain how to perform other common tasks with Google Sheet queries:
Google Sheets Query: How to Select Multiple Columns
Google Sheets Query: Select Rows that Contain String
Google Sheets Query: How to Use Cell Reference in Formula
In your examples above, how would you gather data for more than one option in the same cell. For example, if you wanted to see the Mavericks, Hawks and Nets in a query.
Hi Peggy…To query for multiple criteria in Google Sheets, such as filtering for “Mavericks,” “Hawks,” and “Nets,” you can use the `OR` operator in your `QUERY` function.
Here’s how you can do it:
### Example:
Assuming your data is in columns A and B, with column A containing the team names and column B containing some statistics, here’s how you can query for “Mavericks,” “Hawks,” and “Nets” in the same query:
“`excel
=QUERY(A1:B, “SELECT * WHERE A = ‘Mavericks’ OR A = ‘Hawks’ OR A = ‘Nets'”, 1)
“`
### Breakdown:
– `A1:B`: The range where the data is located.
– `SELECT *`: This selects all columns.
– `WHERE A = ‘Mavericks’ OR A = ‘Hawks’ OR A = ‘Nets’`: This filters the data to return rows where column A contains either “Mavericks,” “Hawks,” or “Nets.”
### Another Approach: Using `IN` clause
Instead of chaining multiple `OR` conditions, you can use the `IN` clause, which simplifies the query:
“`excel
=QUERY(A1:B, “SELECT * WHERE A IN (‘Mavericks’, ‘Hawks’, ‘Nets’)”, 1)
“`
This is more compact and achieves the same result, pulling rows that match any of the specified teams.
Let me know if you need further clarification!