Today, we will look at how we can apply a query cell reference in Google Sheets. To give a better understanding, we will use multiple scenarios and examples to cover different situations as broadly as possible.
Let’s get started.
Understanding QUERY: The Basics
Any general query in Google Sheets utilizes the QUERY function, and this function is the core of our article today. The function is easy to learn but hard to master.
The QUERY function syntax:
QUERY(data, query, [headers])
- data: The range of cells on which we are going to be performing a query.
- query: The logical text that is used to determine and perform the query. Since it is a text, it must be enclosed in quotation marks (“”).
- [headers]: Optional. Determines the number of header rows included in the selection (data).
The QUERY function uses clauses, some of which we will see the use of later in this article.
With the basics out of the way, let’s see how we can use this function with cell references.
Point to note
String conditions of a QUERY need to be enclosed in single quotes (‘’) whereas numerical values don’t.
How to Use QUERY with Cell Reference in Google Sheets
1. Comparison Query with Cell Reference in Google Sheets
We start simple with general logical conditions to compare and extract data.
We have six logical operators available to us:
|>=||Greater Than and Equal To|
|<=||Less Than and Equal to|
|<>||Not Equal To|
But for our example, we are only going to be using a couple of them since the query command is the same for all the operators.
We will be using the following table for our example. We have kept the parameters to Name and Sales number for the sake of simplicity.
For our first query, we will try to extract all the Names that have a Sales number greater than 500.
- B3:C12: Our data range
- Select *: Returns all values in the row that satisfies the query.
- where C >”&E3: Our logical condition. We have concatenated the cell reference E3 with an ampersand (&) to the text portion of the query.
This formula returns the entire row of values that satisfies our query condition of Sales number greater than 500, as is designated in cell E3.
We can do the same for less than 500 sales condition, and also for other logical operators.
2. Advanced Comparison Query with Cell Reference in Google Sheets
Logical operators are the basics of any comparison condition, but the QUERY function allows us to do much more than that with the help of clauses.
Clauses are special string commands that determine query conditions. There are many clauses, but for the sake of today’s article on using cell references in a query, we have chosen the following clauses to work with:
- Starts With
- Ends With
As you can see, the purpose of these clauses is pretty self-explanatory and we will go through them like a breeze.
For the following examples, we will be using this worksheet:
Again, the idea of the table is simple. We will use the clauses to extract information according to the Product ID. We will also display the Cost in our output.
I. Using Starts With Clause with Cell Reference
The first clause we have on our list is Starts With. It checks for and returns matches that start with the given value. For example, we want to find all the entries in our table that start with the string “TX”.
=QUERY(B3:C12,"Select * where B Starts With '"&E3&"'")
II. Using Ends With Clause with Cell Reference
On the other end of the spectrum, we have the Ends With clause. This clause checks and returns matches that end with the given value. For example, we want to find all the entries in our table that end with the string “WH03”.
=QUERY(B3:C12,"Select * where B Ends With '"&E3&"'")
III. Using Contains Clause with Cell Reference
Next, we have the Contains clause. Unlike the Starts With and Ends With clauses, Contains checks the whole cell for a matching value. Meaning, it checks for the whole string or the substring in the cell.
In this example, we will look for all the entries that contain the string “Box”.
=QUERY(B3:C12,"Select * where B Contains '"&E3&"'")
IV. Using Matches Clause with Cell Reference
The Matches clause is similar to Contains but goes one step ahead with it being able to utilize regular expressions.
With regular expressions, we can build upon our search query and apply multiple criteria.
Continuing from a previous example, let’s say we want to look up entries with two different strings, “WH01” and “WH04”. We have to use regular expression to define that in our cell reference (E3):
=QUERY(B3:C12,"Select * where B Matches '"&E3&"'")
For a better understanding of the Matches clause with regular expression, please follow this link.
V. Using Like Clause with Cell Reference
Another alternative to the Contains clause is Like. And unlike the Matches clause, like uses wildcards as its search conditions.
Starting off simple, we will use the percentage (%) wildcard to extract all entries that contain the word “Paper”.
Search Key for Like:
The percentage (%) before and after “Paper” denotes that there may be string values in their place.
=QUERY(B3:C12,"Select * where B Like '"&E3&"'")
Another wildcard we can use to show the versatility of Like is the underscore (_). It represents that any value can take its place. For example, if we want all the entries that have an ID that starts with “TX1” then any number followed by the item “Box”, our Key will be:
=QUERY(B3:C12,"Select * where B Like '"&E3&"'")
QUERY itself is quite a versatile function in any spreadsheet application. It becomes more useful when you know how to query with cell reference in Google Sheets. We hope that we have covered its uses with enough examples and scenarios for you to clearly understand and utilize them for your spreadsheets.
Please feel free to leave any questions or advice you might have for us in the comments section below.