The easiest way to use multiple OR in Google Sheets Query is using the matches (preg) regular expression match, not the OR logical operator.
Of course, to test several conditions within a column we can also use the OR logical operator in Query. The conditions can be as a list from range or within the formula.
In this new iteration of Google Sheets Query formula examples, I am explaining that cool tips.
Must Read: Matches Regular Expression Match in Google Sheets Query.
Using OR Logical Operator
To test the multiple OR criteria/conditions in Query, I want sample data. Let me help you to create one sample.
I am going to import the required data from an external page (source) using the IMPORTHTML formula as below.
=IMPORTHTML("https://en.wikipedia.org/wiki/List_of_BWF_World_Championships_medalists","table",2)
Enter the above formula in cell A1 of a blank Sheet. It’s a list of BWF World Championships men’s singles medalists.
The formula will populate the below data and in which I am going to apply the multiple OR criteria in Query.
I am filtering column A which contains the years of the title win to explain how to use multiple OR conditions in Query.
=query(A1:E,"Select * where A=2015 or A=2017 or A=2018")
The above multiple OR logical operator in Query help me filter the rows that contain the years 2015, 2017, and 2018. Here is the output.
You can increase the number of conditions aka criteria easily using additional OR logical operators. But that will make the formula much longer.
To make the formula simpler you can use the Matches string comparison operator in Query. See that tip.
Using Matches String Comparison Operator
See how Matches replaces OR in Query.
=query(A1:E,"Select * where A matches '2015|2017|2018'")
This formula is pretty cool, right? You can use either of the methods to filter multiple matching values in Google Sheets Query.
In the next example, I am going to shed some light on how to use multiple OR conditions as a list from range.
Multiple OR Conditions as a List in Google Sheets Query
In the above shared two Query formulas, the conditions are within the
The above conditions are in the range F1:F3. How to use this OR criteria list in Query?
I am using the matches regular expression match here as its simple and more elegant.
The first step is to join the criteria to get an output as '2015|2017|2018'
. This you can achieve by using the JOIN function and ampersand.
="'"&join("|",F1:F3)&"'"
Use this formula in Query as the conditions. It would be like this.
=query(A1:E,"Select * where A matches "&"'"&join("|",F1:F3)&"'")
This offers more flexibility in Query criteria use. That’s all!
If you find this tutorial useful, please take a moment to share it with your colleagues and friends. You can use the social share buttons below. Thanks.