HomeGoogle DocsSpreadsheetMultiple CONTAINS in WHERE Clause in Google Sheets Query

Multiple CONTAINS in WHERE Clause in Google Sheets Query

Published on

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)
Query Contains and Not Contains String Matching

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)
Example to Multiple CONTAINS in WHERE Clause in Query

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

AB
1Product CodesCriteria
2AB
3DJ

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!

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

11 COMMENTS

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

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

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

LEAVE A REPLY

Please enter your comment!
Please enter your name here