HomeGoogle DocsSpreadsheetHow to Use Nested Queries in Google Sheets

How to Use Nested Queries in Google Sheets

Published on

This article describes Google Sheets nested Query usage.

Before proceeding, as a side note, if you have not seen my earlier awesome collection of Query tutorials, use the term query to search.

Let’s begin! In a nested Query formula in Google Sheets, a Query is written inside another Query.

The result of the Subquery or we can say the inner Query is used to execute the outer Query. Without examples, you will definitely find it difficult to digest.

I have two examples on this page related to this topic.

Google Sheets Nested Query

Create three tables (you can copy-paste from here) as below in your Google Sheets. Do stick with the range wherever mentioned.

Tables for Testing

Table 1: Product

This data must be copied/entered in the range A2:D7 on ‘Sheet1’.

P_IDP_NAMEQTYRATE
P1000Broccoli
P2000Red Cabbage
P3000Cauliflower
P4000Cucumber
P5000Tomatoes

Table 2: Vendor

Data for the range F2:G6 on the same sheet.

V_IDV_NAME
V1Vendor A
V2Vendor B
V3Vendor C
V4Vendor D

Table 3: Product and Vendor

This is the last table. This must be copied to the range I2:J8 on ‘Sheet1’.

P_IDV_ID
P1000V1
P1000V3
P2000V1
P3000V2
P4000V2
P5000V3

Problems to Solve

I am going to solve the below two problems using nested Query formulas in Google Sheets.

  1. Return Product ID (P_ID) using Vendor Name (V_Name).
  2. Return Product Name (P_Name) using Vendor Name (V_Name).

To solve the above problems we may require to use the SQL similar IN operator in Google Sheets nested Query formula.

Without learning the above said IN usage we can’t continue. Here you go!

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

If you have understood the IN/NOT IN Query usage, we can proceed further.

SQL Similar IN Operator in Nested Query in Google Sheets

Nested Query Formula with One Subquery

Let’s address the problem # 1. What we want is to return the P_ID using V_Name.

That means we have the criteria for the same from Table # 2 which are “Vendor A” and “Vendor C”.

First, filter table 2 and extract the V_ID from column 1.

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

Query Formula Two OR Criteria as IN Optr

P_IDs which we want to extract are available in Table # 3 (Product and Vendor)

We can filter column 1 of table # 3 using the above V_IDs as the criterion.

Without nesting, the formula would be like this which is also an example of SQL similar IN operator use in Google Sheets.

Query Formula without Nesting:

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

Assume “V1” is in cell L1 and “V3” is in cell M1. If so, we can rewrite the above query as;

=query(I3:J,"Select I where J matches '"&TEXTJOIN("|",true,L1:M1)&"'")

We can replace L1:M1 with our subquery above.

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 One Subquery

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

Nested Query Formula with Two Subqueries

Here in this example, I am going to solve our Problem 2.

We want to find the product names supplied by “Vendor A” or “Vendor C”.

This involves three tables. In the first step, we will filter the vendor id (V_ID) using the vendor names (V_NAME) from table 2.

Then use that V_IDs to filter the P_IDs from table 3.

These two steps we have already completed in the just above nested Query formula.

Now in the third step, we are going to use the above-nested Query as the Subquery in another Query.

Without the Subquery, the formula will be as follows.

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

Needless to say, the range L2:L5 contains the earlier Google Sheets nested Query formula result.

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 tried my level best to explain nested Query usage in Google Sheets. Any doubt or error, please point out.

Example Sheet

In certain cases, for example, to solve the above problem 1 and 2, there is no need to use Google Sheets nested Queries as above.

We can use Vlookup and sometimes reverse Vlookup (in a nested form) as an alternative Subqueries/nested Queries.

We will discuss that in a different tutorial later.

Note: When nesting Queries please follow the Query literal use.

Recommended Reading

  1. Multiple CONTAINS in WHERE Clause in Google Sheets Query.
  2. Simple Comparison Operators in Sheets Query.
  3. How to Use And, Or, and Not in Google Sheets Query.
  4. Ends with and Not Ends with Suffix Match in Query.
  5. Starts with and Not Starts with Prefix Match in Query.
  6. How to Sum, Avg, Count, Max, and Min in Google Sheets Query.
  7. How to Use Arithmetic Operators in Query in Google Sheets.
  8. Matches Regular Expression Match in Google Sheets Query.
  9. How to Use Not Equal to in Query in Google Sheets.
  10. CONTAINS Substring Match in Google Sheets Query for Partial Match.
  11. How to Use LIKE String Operator in Google Sheets Query.
Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing 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.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

2 COMMENTS

LEAVE A REPLY

Please enter your comment!
Please enter your name here