How to Use Multiple OR in Google Sheets Query

Published on

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.

Multiple OR criteria in Sheets 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.

Output of Multiple OR in Google Sheets Query

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 formula. Now I want to use that as a list from range.

Query OR Conditions as a List

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.

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

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.