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:

QUERY(data, query, [headers])

query function syntax for query cell reference in google sheets

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:

Symbol Description
= Equal To
> Greater Than
< Less Than
>= 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.

worksheets for query cell reference in google sheets

For our first query, we will try to extract all the Names that have a Sales number greater than 500.

Our formula:

=QUERY(B3:C12,”Select * where C >”&E3,1)

using query for cell reference in google sheets with logical condition

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:

new worksheet for query cell reference in google sheets

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:

=QUERY(B3:C12,"Select * where B Starts With '"&E3&"'")

using the starts with clause of query in google sheets

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:

=QUERY(B3:C12,"Select * where B Ends With '"&E3&"'")

using ends with clause of query in google sheets

Similar Readings

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:

=QUERY(B3:C12,"Select * where B Contains '"&E3&"'")

using contains clause for query in google sheets

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:

=QUERY(B3:C12,"Select * where B Matches '"&E3&"'")

using matches clause for query cell reference in google sheets

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:

=QUERY(B3:C12,"Select * where B Like '"&E3&"'")

using like clause with percentage wildcard

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:

=QUERY(B3:C12,"Select * where B Like '"&E3&"'")

using like clause with underscore

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

Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

We will be happy to hear your thoughts

Leave a reply