I know you are a Query function enthusiast. Here is an awesome tip for you! I am going to solve your problem of using multiple CONTAINS in WHERE Clause in Google Sheets Query.
Other than aggregation, Google Sheets Query has an awesome capability which is string matching. For that, there are simple comparison operators and five complex string comparison operators. They are CONTAINS, MATCH, LIKE, STARTS WITH, and ENDS WITH.
Some of you may have already well aware of the use of CONTAINS as well as doesn’t CONTAINS use in Query. Here is one example.
Contains in Where Clause Example Formula in cell D1: Extracts all the ‘Product Codes’ which contain the string ‘AB’.
=QUERY(A1:A,"Select * Where A Contains 'AB' ",1)
Not Contains Example Formula in cell E1: Extracts all the ‘Product Codes’ which does not contain the string ‘AB’.
=QUERY(A1:A,"Select * Where not A Contains 'AB' ",1)
The purpose the said string matching operator is partial matching of a criterion. With the Query NOT logical operator, you can reverse the matching. I have demonstrated that in the above second formula example.
The purpose of multiple CONTAINS in Query WHERE clause is to use multiple substring match, right? Let me address that issue, which is our main topic of this post.
Multiple Conditions in CONTAINS in WHERE Clause in Query Function
What about using multiple CONTAINS in WHERE Clause in Google Sheets Query? Can you write a CONTAINS substring match with multiple criteria?
The answer is YES! You can use multiple criteria in CONTAINS as well as in NOT CONTAINS in WHERE Clause in Google Sheets Query. Then how to write multiple CONTAINS in Query?
Here is the answer! If you have a few partial matching (or not matching) keywords, then use multiple CONTAINS in Query. If the number of partial matching (or not matching) keywords is large there is a better option. First, see the multiple CONTAINS use.
=QUERY(A1:A,"Select * Where A Contains 'AB' or A Contains 'DJ' ",1)
You can replace the CONTAINS in Query Where Clause with MATCHES in Query Where Clause in Sheets.
That means when you want to use multiple criteria in CONTAINS in Where Clause, you can use the MATCH operator.
How to Replace CONTAINS with MATCHES in Google Sheets Query
I have given two single criterion formulas above. I am rewriting those formulas using the MATCHES in Query Where Clause.
Matches in Where Clause (Example Formula in cell D1):
=QUERY(A1:A,"Select * Where A Matches '.*AB.*' ",1)
Not Matches (Example Formula in cell E1):
=QUERY(A1:A,"Select * Where not A Matches '.*AB.*' ",1)
When replacing CONTAINS with MATCHES, do include .*
around the criterion. Check my two Query formulas above to learn that use.
As I have mentioned above, multiple CONTAINS in Query function is possible using the MATCHES string matching.
In the above example Query formulas, I have the criterion/condition within the formula. If the criterion is within a cell, then how to refer to that cell in Query? See that useful tips on my earlier Google Sheets guide here – How to Use Cell Reference in Google Sheets Query.
Multiple CONTAINS Using MATCHES in WHERE Clause in Google Sheets Query
In the earlier examples, I have extracted the ‘Product Codes’ that contain/doesn’t contain the substring ‘AB’. Now I am going to write multiple substring match/mismatch in Query formula.
See 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 criterion CONTAINS with multiple criteria MATCHES, other than the .*
around criterion, include a |
separator between the string. That makes things easy for us to include several partial matching (not matching) criteria in a single Query.
MATCHES based string matching is my answer to how to use multiple CONTAINS in WHERE Clause in Google Sheets Query.
Matches Conditions as a Range in Google Sheets Query Function
Assume the conditions (in the last Query formula above) are entered as a range. Then how to code the Query formula correctly?
Criteria/Conditions in B2:B3
A | B | |
1 | Product Codes | Criteria |
2 | AB | |
3 | DJ |
We should format these criteria as '.*AB.*|.*DJ.*'
to use it in Google Sheets Query regular expression match. How?
Let’s make use of the TextJoin function.
=ArrayFormula("'"&textjoin(".*|",1,".*"&B2:B3)&".*'")
Use this formula in the Query Matches.
=Query(A1:A,"Select A where not A matches "&ArrayFormula("'"&textjoin(".*|",1,".*"&B2:B3)&".*'"))
Before concluding, one bonus tip. Do you know how to use CONTAINS and does not CONTAINS in a single Query?
Contain and Does Not Contain in a Single Query Formula
Here for a single condition Contain and Does Not Contain, you can use the CONTAINS in Where Clause. For single as we all as multiple conditions (matches/mismatches) use the MATCHES.
Single Condition Contain and Does Not Contain (You can replace CONTAINS in this formula using MATCHES in 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 outputs. That’s all. Enjoy!
I am hoping you can help. I am trying to populate data based on two separate requests. Column E has school grades (EE3, EE4, Kindergarten, 1st grade, etc.), and Column H has testing requested (speech, cognition, dyslexia, etc.). I need two kinds of data to populate into one spreadsheet:
1. If the school grade is not EE3 or EE4, no matter the testing requested.
2. If the school grade is EE3 or EE4 and they are only requesting speech.
I can get the first populated, but I cannot seem to figure out how to add the second request of “if/then” to the same spreadsheet.
Hi Ashley Morland,
You can try the following QUERY formula:
=QUERY({'Sample Sheet'!E:H},"Select * where (Col1 matches 'EE3|EE4' and Col4='speech') or
(not(Col1) matches 'EE3|EE4' and Col1 is not null)")
You need to make two changes in this formula:
1. Replace
Sample Sheet
with the actual sheet name containing the source data.2. Replace commas with semicolons as per your LOCALE settings.
Ahhh!! You are amazing! Thank you so much!
I’ve searched long and hard for an answer to this question – thank you for sharing your knowledge!
Hello! This solution helped me in my report.
The only thing I can’t figure out with the formula is instead of B2:B3, how to use a larger range, for example, B2:B20, and it has only five values.
How do you factor in the blank cells?
Looking forward to your answer.
Hi, Anne S.,
For that, we can use FILTER.
I mean, replace
B2:B3
withfilter(B2:B20,len(B2:B20))
orfilter(B2:B,len(B2:B))
Hi Prashant
How can we add multiple Arrayformula in the same query? Is it possible?
Hi, Nihal,
I was hoping you could show me your expected result based on sample data in a Sheet. You can share the URL below.
You have made my wildest dreams come true with the multiple matches info!
The array formula you used to match conditions as a range is genius. Thank you so much for sharing. I haven’t seen this solution anywhere else.
Hi, Kevin Wells,
Thanks for your feedback.