How to Apply Multiple OR Conditions in Google Sheets QUERY

Published on

You can join multiple conditions using the OR logical operator in QUERY to match either of the conditions. However, using OR is not ideal when dealing with many conditions, as it requires multiple OR statements. The easiest way to apply multiple OR conditions in Google Sheets QUERY is by using the MATCHES (preg) regular expression match instead of the OR logical operator.

In this tutorial, I will cover both approaches. You can use OR when there are only two or three conditions. However, if you have more conditions or need to use a list, I recommend using MATCHES.

Applying Multiple OR Conditions in QUERY Using OR Logical Operator

Below is a sample dataset with flight timings from Dubai to various destinations. Columns A to D contain the destination, departure time, arrival time, and duration.

Sample Data

Although this is a sample dataset, please note one important detail: The departure and arrival times are in local time zones. Flight durations account for time zone differences, but this does not affect how we apply multiple OR conditions in Google Sheets QUERY.

Example 1: Hardcoded OR Conditions

Assume you want to filter destinations Singapore, London, and Mumbai:

=QUERY(A1:D, "select * where A='London' or A='Singapore' or A='Mumbai'", 1)

This formula applies multiple OR conditions using the OR logical operator in the QUERY function.

Example 2: Using Cell References for OR Conditions

If the destinations to filter are in F1, F2, and F3, use the following formula:

=QUERY(A1:D, "select * where A='"&F1&"' or A='"&F2&"' or A='"&F3&"'", 1)
Example of applying multiple OR conditions in Google Sheets QUERY using the OR logical operator

If you are unfamiliar with using different literals in QUERY, check out: Literals in Google Sheets QUERY – How to Use Them.

Applying Multiple OR Conditions in QUERY Using MATCHES

Now, let’s see how MATCHES can replace multiple OR logical operators in the QUERY function.

Example 3: Using MATCHES with Hardcoded Values

To replace the first formula above where the city names are hardcoded, use:

=QUERY(A1:D, "select * where A matches 'London|Singapore|Mumbai'", 1)

This approach is simpler because you only need to separate each condition with a pipe (|).

Example 4: Using MATCHES with a Cell Range

If the criteria are listed in F1:F3, you can use:

=QUERY(A1:D, "select * where A matches '"&TEXTJOIN("|", TRUE, F1:F3)&"'", 1)

Here, TEXTJOIN is used to combine the conditions with a pipe:

TEXTJOIN("|", TRUE, F1:F3)

We specify it as a string literal by adding "'& on the left and &'" on the right.

Benefits of Replacing OR Logical Operators with MATCHES

Using MATCHES instead of OR logical operators has several advantages.

When hardcoding conditions, MATCHES makes the formula cleaner and easier to read.

When using a cell range for criteria, applying multiple OR conditions with the OR logical operator can become cumbersome. Instead of manually specifying each condition, you can refer to the range dynamically using TEXTJOIN. Additionally, you can make the range open-ended (e.g., F1:F) instead of specifying a fixed range like F1:F3.

Additional Tips

All the above formulas are case-sensitive. To make them case-insensitive, convert the column to lowercase using the LOWER function and ensure the criteria are also in lowercase. Here’s an example:

=QUERY(A1:D, "select * where lower(A) matches 'london|singapore|mumbai'", 1)

Resources

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.

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

How to Filter by Total in Google Sheets Pivot Tables

Google Sheets offers many tools to summarize and analyze data, but Pivot Tables are...

More like this

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

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.