Do you want to master the art of using cell reference in Google Sheets Query? Look no further! In this tutorial, we will guide you through the process of incorporating cell references as criteria in the WHERE clause of your Google Sheets Query formula. Trust us, it’s easier than you think!
Why Use Cell Reference in Google Sheets Query
The WHERE clause in Google Sheets Query function allows you to extract specific rows that match a particular condition. While you can input the condition directly into the Query formula, we will focus on using cell references for a more dynamic approach.
But before we dive into the process, please note that in the Query SELECT, PIVOT, and GROUP BY clauses, we use column identifiers like A, B, or Col1, Col2, etc., instead of cell references. This distinction is important, so don’t get confused!
Simple Comparison Operators with Cell Reference
In our tutorial on the use of literals in Query, we covered simple comparison operators such as <=, <, >, >=, =, !=, <>. Literals in this context refer to values like strings, numbers, Boolean TRUE/FALSE, or different date/time types used for comparisons or assignments.
For example, consider the following Query formula with a date literal:
=Query(A2:B,"Select * where A < date '2019-08-31'")
To use a cell reference as a date literal in Query, you can modify the formula as follows:
=Query(A2:B,"Select * where A <= date '"&TEXT(D1,"yyyy-mm-dd")&"'")
For more examples involving other literals, check out our comprehensive tutorial on the subject.
Complex Comparison Operators with Cell Reference
Complex comparison operators allow for string and substring comparisons within Query. We will cover the following operators: Contains, Matches, Like, Starts With, and Ends With. In our tutorials, we previously explained how to use these operators but did not address the use of cell references as criteria in the WHERE clause. Let’s rectify that!
Contains Operator with Criterion from a Cell
Let’s say we want to filter values in column A based on a dynamically changing material name. We can achieve this by using the Contains substring match in a Query formula, as shown below:
=query(A2:A,"Select A where A contains'"&C1&"'",0)
Please refer to our tutorial on CONTAINS substring matching in Google Sheets Query for a more detailed exploration of this topic.
Matches Operator with Criterion from a Cell
The Matches operator in Query uses regular expressions for matching. If you are unfamiliar with regular expressions, we strongly recommend reading our introductory tutorial. Below are two variations of the regular expression match using a cell reference as the criterion:
=query(A2:A,"Select A where A Matches '"&C1&"'",0)
Like Operator with Criterion from a Cell
The Like operator in Query allows for pattern matching. Before we proceed, please consult our tutorial on using the LIKE string operator in Google Sheets Query. Once you are familiar with the available wildcard characters, consider the following examples:
When using the percentage wildcard for multiple character matches:
=query(A2:A,"Select A where A Like'"&C1&"'",0)
When using the underscore wildcard for single-character matches:
=query(A2:A,"Select A where A Like'"&C1&"'",0)
Starts With and Ends With Conditions from a Cell
Let’s wrap up our tutorial with two more formulas—this time focusing on the Starts With and Ends With operators in Query. These operators allow you to return strings in column A that either start or end with a specific substring. Simply input the relevant substring into cell C1, and use the following formulas:
Starts With syntax:
=query(A2:A,"Select A where A Starts With'"&C1&"'",0)
Ends With syntax:
=query(A2:A,"Select A where A Ends With'"&C1&"'",0)
Remember to adjust the criterion in cell C1 accordingly to match your desired results.
Conclusion
Congratulations on learning how to use cell references in Google Sheets Query! You can take your formulas to the next level by making them case-insensitive with the LOWER function and lowercase letters in the criteria. Additionally, you can negate string matching using the NOT operator in Query.
We hope this tutorial has empowered you to harness the full potential of Google Sheets Query. For more tips, tricks, and tutorials, head over to Crawlan.com. Happy querying!
