How to Use Nested Queries in Google Sheets

Published on

This article explains how to use nested Query formulas in Google Sheets.

In a nested Query formula, one Query is placed inside another. The result of the subquery (or inner Query) is used as the input for the outer Query. This concept can be challenging to grasp without examples.

Fortunately, I’ve included two examples on this page to help clarify the process.

Before we dive in, if you haven’t checked out my earlier collection of Query tutorials, I recommend searching for the term “Query” on my site.

Let’s get started!

Google Sheets Nested Query

To get started, create three tables in your Google Sheets as shown below. Make sure to follow the specified ranges.

Table 1: Product

Enter the following data in the range A2:D7 on ‘Sheet1’:

P_IDP_NAMEQTYRATE
P1000Broccoli
P2000Red Cabbage
P3000Cauliflower
P4000Cucumber
P5000Tomatoes

Table 2: Vendor

Enter the following data in the range F2:G6 on the same sheet:

V_IDV_NAME
V1Vendor A
V2Vendor B
V3Vendor C
V4Vendor D

Table 3: Product and Vendor

Finally, enter the following data in the range I2:J8 on ‘Sheet1’:

P_IDV_ID
P1000V1
P1000V3
P2000V1
P3000V2
P4000V2
P5000V3

Example Sheet

Problems to Solve

I will solve the following two problems using nested Query formulas in Google Sheets:

  1. Return the Product ID (P_ID) using the Vendor Name (V_NAME).
  2. Return the Product Name (P_NAME) using the Vendor Name (V_NAME).

To solve these problems, we may need to use an SQL-like IN operator in the Google Sheets nested Query formula.

Before continuing, it’s essential to understand how to use the IN operator in Google Sheets Queries. Let’s dive in!

The Alternative to the SQL IN Operator in Google Sheets Query (Also NOT IN)

Once you understand how to use the IN/NOT IN Query, we can proceed with solving the problems.

SQL Similar IN Operator in Nested Query in Google Sheets

Nested Query Formula with One Subquery

Let’s tackle problem #1: Returning the Product ID (P_ID) using the Vendor Name (V_NAME). This will help identify which products the vendor is selling.

The V_NAME (vendor name) is in Table #2, and the P_ID (product ID) is in Table #3. The common column between these two tables is the V_ID.

First, we’ll filter Table 2 to extract the V_ID (the common field in both tables) from column F that matches “Vendor A” and “Vendor C.”

Query Formula with Two OR Criteria as IN Operator

Subquery Formula:

=QUERY(F3:G, "Select F where G = 'Vendor A' or G = 'Vendor C' ")

This subquery formula will return the V_IDs “V1” and “V3.”

Next, we’ll nest this Query formula within another Query formula. Before nesting, let’s test the formulas separately. Now, onto the second formula.

We can filter column I of Table #3 using the V_IDs obtained from the subquery as the criterion.

Without nesting, the formula would look like this, which is also an example of using an SQL-like IN operator in Google Sheets.

Query Formula without Nesting:

=QUERY(I3:J, "Select I where J matches 'V1|V3' ")

Assume “V1” is in cell N3 and “V3” is in cell N4. If so, we can rewrite the above query as:

=QUERY(I3:J, "Select I where J matches '"&TEXTJOIN("|", TRUE, N3:N4)&"' ")

Now, we can replace N3:N4 with our subquery formula.

Query Formula with Nesting:

=QUERY(I3:J, "Select I where J matches '"&TEXTJOIN("|", TRUE, QUERY(F3:G, "Select F where G = 'Vendor A' or G = 'Vendor C' "))&"' ")
Google Sheets Nested Query with a Single Subquery

If you enter it in cell L2, it will return the values P1000, P1000, P2000, and P5000 in the range L2:L5.

The formula above is an example of a nested Query in Google Sheets.

Nested Query Formula with Two Subqueries

Let’s tackle Problem #2: Returning the Product Name (P_NAME) using the Vendor Name (V_NAME). This will help identify which products are associated with the specified vendors.

We want to find the product names supplied by “Vendor A” or “Vendor C.” The vendor names are in Table #2, Product Names are in Table #1. There is no common field in these two tables. So what we will do here is to use our previous formula which returns the Product IDs of the said two vendors and use that to filter the product names in Table #1.

The range L2:L5 contains the Product IDs of the products supplied by “Vendor A” and “Vendor C”. Let’s filter the product names associated with these IDs in Table #1.

Without the subquery, the formula would look like this:

=QUERY(A3:D, "Select B where A matches '"&TEXTJOIN("|", TRUE, L2:L5)&"' ")

Here, the range L2:L5 contains the result of the previous Google Sheets nested Query formula.

To incorporate the nested Query, simply replace the range L2:L5 with the nested Query:

=QUERY(A3:D, "Select B where A matches '"&TEXTJOIN("|", TRUE, QUERY(I3:J, "Select I where J matches '"&TEXTJOIN("|", TRUE, QUERY(F3:G,"Select F where G = 'Vendor A' or G = 'Vendor C'"))&"' "))&"' ")
Google Sheets Nested Query with Two Subqueries

I’ve done my best to explain the usage of nested Queries in Google Sheets. If you have any questions or notice any errors, please let me know.

Additional Notes

In some cases, such as solving Problems 1 and 2 above, there is no need to use nested Queries in Google Sheets.

As an alternative, you can use VLOOKUP or sometimes a reverse VLOOKUP (in a nested form) instead of subqueries or nested Queries.

We will discuss this approach in a separate tutorial later.

Note: When nesting Queries, please be mindful of the proper use of Query literals.

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

2 COMMENTS

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.