Hey there, my friends! I know you are passionate about the Query function. Well, I have a brilliant trick for you! I am here to solve your problem when it comes to using multiple CONTAINS in the WHERE clause of Query in Google Sheets.
In addition to aggregation, the Query function in Google Sheets has an amazing capability: string matching. To achieve this, there are simple comparison operators and five complex string comparison operators: CONTAINS, MATCHES, STARTS WITH, ENDS WITH, and EQUALS.
Some of you might already know how to use CONTAINS and the negation of CONTAINS in Query. Here’s an example:
Formula Example with CONTAINS in cell D1: Extract all “Product Codes” that contain the string “AB”.
=QUERY(A1:A,"SELECT * WHERE A CONTAINS 'AB'",1)Formula Example with NOT CONTAINS in cell E1: Extract all “Product Codes” that do not contain the string “AB”.
=QUERY(A1:A,"SELECT * WHERE NOT A CONTAINS 'AB'",1)
The purpose of the string matching operator is to perform a partial match according to a criterion. Thanks to the logical NOT operator in Query, you can reverse the matching. I demonstrated this in the second formula example above.
Now, let’s get into the main topic of this article: using multiple CONTAINS in the WHERE clause of Query.
Multiple CONTAINS in the WHERE clause of the Query function
So, what about using multiple CONTAINS in the WHERE clause of Google Sheets Query? Can you perform partial matching with multiple criteria?
The answer is YES! You can use multiple criteria with CONTAINS and NOT CONTAINS in the WHERE clause of the Query function in Google Sheets. Now, how do you write multiple CONTAINS in Query?
Well, here’s the answer! If you have a few partial match (or non-match) keywords, use multiple CONTAINS in Query. If the number of partial match (or non-match) keywords is high, there is a better option. Let’s first look at the usage of multiple CONTAINS.
=QUERY(A1:A,"SELECT * WHERE A CONTAINS 'AB' OR A CONTAINS 'DJ'",1)You can replace CONTAINS in the WHERE clause of Query with MATCHES in the WHERE clause of Sheets.
This means that when you want to use multiple criteria with CONTAINS in the WHERE clause, you can use the MATCHES operator.
How to replace CONTAINS with MATCHES in Google Sheets Query
I provided two single-criteria formula examples above. I will rewrite them using the MATCHES operator in the WHERE clause of Query.
MATCHES in the WHERE clause (formula example in cell D1):
=QUERY(A1:A,"SELECT * WHERE A MATCHES '.*AB.*'",1)NOT MATCHING (formula example in cell E1):
=QUERY(A1:A,"SELECT * WHERE NOT A MATCHES '.*AB.*'",1)When replacing CONTAINS with MATCHES, include .* around the criterion. Take a look at my two Query formulas above to see how to use them.
As mentioned earlier, it is possible to use multiple CONTAINS in the Query function using string matching.
In the formula examples above, I included the criterion/condition in the formula. If the criterion is in a cell, how do you reference it in Query? Check out my useful tips in my previous guide on using cell references in Google Sheets Query.
Multiple CONTAINS using MATCHES in the WHERE clause of Google Sheets Query
In the previous examples, I extracted “Product Codes” that contain/do not contain the sub-string “AB”. Now, let’s write multiple matches/mismatches of sub-strings in the Query formula.
Here are the following formulas:
D1: =QUERY(A1:A,"SELECT * WHERE A MATCHES '.*AB.*|.*DJ.*'",1)
E1: =QUERY(A1:A,"SELECT * WHERE NOT A MATCHES '.*AB.*|.*DJ.*'",1)
When replacing a single CONTAINS criterion with multiple MATCHES criteria, add a separator | between the strings. This makes it easier for us to include multiple partial match (or non-match) criteria in a single Query.
Matching based on criteria is my answer to how to use multiple CONTAINS in the WHERE clause of Google Sheets Query.
Matching conditions as a range in the Query function of Google Sheets
Suppose the conditions (in the last Query formula above) are entered as a range. How do you properly code the Query formula?
Criteria/Conditions in B2:B3:
AB
DJWe need to format these criteria as ‘.AB.|.DJ.’ to use them in the regular expression matching of Google Sheets Query. How?
Let’s use the TextJoin function.
=ArrayFormula("'"&TEXTJOIN(".*|",1,".*"&B2:B3)&".*'")Use this formula in the Query match.
=QUERY(A1:A,"SELECT A WHERE NOT A MATCHES "&ArrayFormula("'"&TEXTJOIN(".*|",1,".*"&B2:B3)&".*'") )Before we conclude, here’s a bonus tip. Do you know how to use CONTAINS and DOES NOT CONTAIN in a single Query formula?
Contains and does not contain in a single Query formula
For a single Contains and Does Not Contain condition, you can use CONTAINS in the WHERE clause. For both single and multiple conditions (matches/mismatches), use MATCHES.
Contains and does not contain for a single condition (you can replace CONTAINS in this formula with MATCHES in the WHERE clause):
=QUERY(A1:A,"SELECT * WHERE A CONTAINS 'ABC' AND NOT A CONTAINS '101'",1)Multiple matches and does not match (Multiple CONTAINS and DOES NOT CONTAIN):
=QUERY(A1:A,"SELECT * WHERE A MATCHES '.*AB.*|.*DJ.*' AND NOT A MATCHES '.*101.*|.*102.*'",1)Apply these formulas in your Google Sheets and check the results. That’s it. Have fun!
If you want to learn more about Google Sheets tricks and other digital marketing tools, check out Crawlan.com.
