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_ID | P_NAME | QTY | RATE |
P1000 | Broccoli | ||
P2000 | Red Cabbage | ||
P3000 | Cauliflower | ||
P4000 | Cucumber | ||
P5000 | Tomatoes |
Table 2: Vendor
Data for the range F2:G6 on the same sheet.
V_ID | V_NAME |
V1 | Vendor A |
V2 | Vendor B |
V3 | Vendor C |
V4 | Vendor D |
Table 3: Product and Vendor
This is the last table. This must be copied to the range I2:J8 on ‘Sheet1’.
P_ID | V_ID |
P1000 | V1 |
P1000 | V3 |
P2000 | V1 |
P3000 | V2 |
P4000 | V2 |
P5000 | V3 |
Problems to Solve
I am going to solve the below two problems using nested Query formulas in Google Sheets.
- Return Product ID (P_ID) using Vendor Name (V_Name).
- 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”.
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'"))&"'")
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'"))&"'"))&"'")
I’ve tried my level best to explain nested Query usage in Google Sheets. Any doubt or error, please point out.
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
- Multiple CONTAINS in WHERE Clause in Google Sheets Query.
- Simple Comparison Operators in Sheets Query.
- How to Use And, Or, and Not in Google Sheets Query.
- Ends with and Not Ends with Suffix Match in Query.
- Starts with and Not Starts with Prefix Match in Query.
- How to Sum, Avg, Count, Max, and Min in Google Sheets Query.
- How to Use Arithmetic Operators in Query in Google Sheets.
- Matches Regular Expression Match in Google Sheets Query.
- How to Use Not Equal to in Query in Google Sheets.
- CONTAINS Substring Match in Google Sheets Query for Partial Match.
- How to Use LIKE String Operator in Google Sheets Query.
U are a gem.
Flipping Genius, Thanks!!!