Learning Google Sheets Query: Using AND/OR for Multiple Criteria

The Google Sheets QUERY function is arguably the most powerful tool available for advanced data manipulation within the spreadsheet environment. It allows users to execute sophisticated data retrieval commands using a dialect similar to standard SQL (Structured Query Language). When working with large datasets, it is rarely sufficient to filter based on a single condition; instead, you need to apply multiple, interdependent criteria. This is achieved by incorporating logical operators, specifically AND and OR, into the query’s WHERE clause. Understanding how to correctly implement these operators is fundamental to mastering complex data analysis in Google Sheets.

Mastering the QUERY Function for Complex Data Retrieval

The core objective of multi-criteria querying is to refine the result set based on rules derived from Boolean logic. The QUERY function syntax requires careful construction of the query string, which defines what data to SELECT and under what conditions to filter (WHERE). When multiple conditions must be evaluated simultaneously, the choice between the AND and OR operators dictates the inclusion criteria for each row of data. Selecting the correct operator is crucial, as using AND when OR is required, or vice versa, will result in an entirely different and potentially misleading filtered dataset.

To illustrate the basic structure, consider a scenario where we are filtering a range defined as A2:C10. The conditions applied in the WHERE clause determine which rows are returned in the final output table. Note that all column references (A, B, C, etc.) within the query string refer to the columns in the specified data range, not necessarily the columns in the main spreadsheet. We will explore how these two foundational operators function and provide concrete examples of their implementation within the Google Sheets environment.

The Mechanics of the AND Logical Operator

The AND operator is used when a record must satisfy all specified conditions simultaneously to be included in the result set. If you link two criteria using AND, a row is only returned if the first criterion evaluates to true and the second criterion also evaluates to true. This operator performs a restrictive filtering action, often narrowing the resulting dataset significantly compared to a single-criterion query.

For example, if we want to retrieve records where Column A contains the string ‘Hello’ and the numeric value in Column C is greater than 10, we utilize the AND structure. This ensures a highly specific result set that adheres strictly to both requirements. Here is the canonical structure for implementing the AND operator:

Example of AND Operator Syntax:

=QUERY(A2:C10, "select A, B, C where A contains 'Hello' and C > 10")

It is important to remember that the comparison operators (such as contains, =, >, <, etc.) must be correctly formatted within the query string. Text strings are enclosed in single quotes, while numeric comparisons are written directly. The use of AND guarantees that the output table will only contain rows that satisfy this dual requirement, making it invaluable for targeted data extraction.

Practical Application of the AND Operator in Google Sheets

To see the AND operator in action, let us consider a sample dataset detailing basketball team performance. This dataset includes information on the Team Name, Conference, Points Scored, and Wins. We aim to identify teams that meet two specific, restrictive conditions: belonging to the ‘East’ conference and scoring over 90 points.

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. In this case, Column B represents the Conference and Column C represents the Points Scored:

=QUERY(A2:D11, "select A, B, C, D where B contains 'East' and C > 90")

The resulting output clearly demonstrates the restrictive power of AND. The query successfully filters the raw data, returning only those rows where both conditions—Conference B containing ‘East’ and Points C being greater than 90—are simultaneously satisfied. The following screenshot illustrates the practical result of executing this query:

Upon reviewing the output, it is clear that only the Magic and the Heat are selected. These teams are the only two records that meet both criteria specified in our query, reinforcing the requirement for dual adherence imposed by the AND operator.

Implementing the Inclusive Logic of the OR Operator

In contrast to the restrictive nature of AND, the OR operator is designed for inclusive filtering. When two criteria are linked using OR, a row is included in the result set if the first condition is true, or the second condition is true, or if both conditions are true. This means that OR significantly expands the potential result set, as it only requires a row to satisfy a minimum of one condition.

The OR operator is particularly useful when you are attempting to segment data based on distinct, yet equally important, characteristics. For instance, we might want to see all teams that belong to the ‘East’ conference regardless of their score, or any team that scored over 90 points regardless of their conference. This flexibility allows for broader data exploration and identification of subsets that satisfy alternative requirements.

Example of OR Operator Syntax:

=QUERY(A2:C10, "select A, B, C where A contains 'Hey' or C = 10")

To demonstrate this inclusive logic, let us revisit the basketball dataset. We will now structure a query using OR to select teams that are either in the ‘East’ conference or have scored more than 90 points. This approach will necessarily yield a larger result set than the previous AND example because fewer constraints are placed on the inclusion of each record.

Once again suppose we have the following dataset in Google Sheets:

The corresponding query utilizes the OR operator:

=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, highlighting the expanded output:

Notice how 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. This outcome confirms the inclusive nature of the OR operator, which is essential for broad data sampling.

Handling Complex Criteria: Operator Precedence and Grouping

While simple queries rely solely on AND or OR, real-world data analysis often requires combining both operators within a single WHERE clause. When mixing operators, understanding operator precedence is critical. In the Google Visualization API Query Language (upon which the Sheets QUERY function is based), the AND operator generally takes precedence over the OR operator, meaning that AND operations are evaluated before OR operations.

To override this default precedence or to ensure absolute clarity in complex filtering, parentheses () must be used. Parentheses allow you to group conditions logically, forcing the query engine to evaluate the enclosed conditions first, much like in traditional relational algebra. For example, if you want teams that are in the East conference AND (either scored over 90 points OR won more than 5 games), the grouping is essential to define the desired logic accurately. Without parentheses, the query engine might incorrectly group the AND condition with only the first part of the OR clause.

Consider the structure required to ensure specific criteria grouping:

• ... where (B = 'East' or B = 'West') and C > 90: This query first evaluates if the conference is East or West, and then checks if the resulting set has scores greater than 90.
• ... where B = 'East' and (C > 90 or D > 5): This query selects teams in the East conference, but only if they satisfy at least one of the two scoring/winning metrics defined within the parentheses.

Mastering the combination of AND, OR, and parentheses ensures that even the most complex filtering requirements can be translated accurately into the Google Sheets QUERY language, yielding reliable and precise data subsets for analysis.

Additional Resources for Advanced Querying

To further enhance your skills in leveraging the full potential of the QUERY function, we recommend exploring the official documentation and advanced tutorials focused on specific filtering requirements.

The following tutorials explain how to perform other common tasks with Google Sheet queries: