How to Query Cell Reference in Google Sheets

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:

Function Breakdown:

• 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:

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.

Our formula:

Formula Breakdown:

• 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
• Contains
• Matches
• Like

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.

Read More: Find Cell Reference in Google Sheets (2 Ways)

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”.

Our formula:

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”.

Our formula:

Similar Readings

• Return Cell Reference in Google Sheets (4 Easy Ways)
• Google Sheets: Use Cell Value in a Formula (2 Ways)

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”.

Our formula:

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):

Our formula:

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.

Our formula:

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:

Our formula:

Read More: Cell Reference From String in Google Sheets

Final Words

QUERY itself is quite a versatile function in any spreadsheet application. It becomes more useful when you know how to query with cell references 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.

Related Articles

• Lock Cell Reference in Google Sheets (3 Ways)
• Relative Cell Reference in Google Sheets
• Dynamic Cell Reference in Google Sheets (Easy Examples)
• Reference Another Tab in Google Sheets (2 Examples)
• Indirect Range in Google Sheets (3 Easy Ways)
• Pull Data From Another Sheet Based on Criteria in Google Sheets (3 Ways)
• Reference Another Workbook in Google Sheets (Step-by-Step)