How to Handle Several OR Columns in Query in Google Sheets

Published on

What you will do if you have several OR columns to handle in Google Sheets Query?

If there are 2-3 columns or let’s say up to ten columns, we can use multiple OR logical operators by specifying each column.

What about if you have 50 columns?

For example, the QUERY ‘data’ has covered the range A1:AX. That means the number of columns is 50.

I want to return column 1 if any of the columns from 6 to 50 contains a specific word.

When you start writing the Query formula, you will find the difficulty. The formula will be very long.

Either of the below Query formulas can be used to test from columns 6 to 10.

=QUERY(Sheet1!A1:AX,"Select A where F='test' or G='test' or H='test' or I='test' or J='test'",1)

or

=QUERY({Sheet1!A1:AX},"Select Col1 where Col6='test' or Col7='test' or Col8='test' or Col9='test' or Col10='test'",1)

We may find it very difficult to handle several OR columns in Query as above.

We may require to use several OR logical operators in this Query formula to test the columns from 6 to 50.

One of the workarounds I have already posted here – Simplify Conditions in Multiple Columns in Query Where Clause in Sheets.

This time I have a much simpler one. Here we go!

Including Several OR Columns in Query (Text Columns)

In a Google Sheets file, I have some data.

The first 5 columns are as follows.

NameAgeClassDivisionTeacher
Johnnie104AJulio
Sarah114AJulio
Micheal104AJulio
Angela104BLeland
Frank114BLeland

I will explain what’s the content in the columns from column 6 to 50 as it’s not possible to include in the table above.

In the next 45 columns, the headers are the sequential dates from 01-Jan-2021 to 14-Feb-2021. That means F1 contains 01-Jan-2021 and AX1 contains 14-Feb-2021.

These columns contain the present (marked P) and absent (marked A) status of the students in column A. Here is a sample screenshot to make you understand the data in question.

Example to Several OR Columns in Query

I want to use Query to return the name of students who were absent on any of the days from 01-Jan-2021 to 14-Feb-2021.

That means I want to handle several OR columns in Query in Google Sheets. Here is how.

As per the above example, I want to return the names “Micheal”, and “Angela” as they were absent on 4-Jan-2021 and 7-Jan-2021 respectively.

It is not easy to handle or include several OR columns in Query in the standard way as explained with an example formula in the beginning.

We can use the below formula for this in another sheet in the same file.

=QUERY({Sheet1!A1:AX,transpose(query(transpose(Sheet1!F1:AX),,9^9))}, "SELECT Col1 WHERE Col51 contains 'A'",1)

Formula Explanation

In cell AY1, insert the below Query. AY is now a helper column. We can avoid using it later.

=transpose(query(transpose(Sheet1!F1:AX),,9^9))

What does this Query do?

It joins columns from column 6 to 50 together.

Combine to Match Multiple OR Columns

This helps us to handle several OR columns in Query in Google Sheets. I’ll tell you how.

Now instead of testing from column 6 to 50 for any of the columns contains the letter “A” (absent), we can test it only in the 51st column as below.

=QUERY({Sheet1!A1:AY}, "SELECT Col1 WHERE Col51 contains 'A'",1)

Instead of Col51='A' I have used Col51 contains 'A' because we require a ‘partial’ match in column 51. The CONTAINS substring match helps us to do this.

To avoid using the helper column AY, in the original formula I have modified the ‘data’ {Sheet1!A1:AY} as below.

{Sheet1!A1:AX,transpose(query(transpose(Sheet1!F1:AX),,9^9))}

So now we can remove the formula in cell AY1.

The above formula won’t always help you to handle several OR columns in Query in Google Sheets. Because we have used partial matching using the CONTAINS substring match.

Assume we want to check if any of the columns contain “apple”.

The above formula would return column 1 where it matches “apple” or “pineapple” in column 6-50.

To avoid this partial matching, let’s replace CONTAINS with MATCHES and use the criteria as below.

=QUERY({Sheet1!A1:AX,transpose(query(transpose(Sheet1!F1:AX),,9^9))}, "SELECT Col1 WHERE Col51 matches '.*\sapple\s.*'",1)

There are no other changes.

Including Several OR Columns in Google Sheets Query (Numeric Columns)

If the columns from 6 to 50 are numeric, then there are no changes in the helper column combine formula.

The only change will be the substring match in Query.

Here also we should use the MATCHES instead of CONTAINS similar to the exact match of strings.

Here is the formula to handle several OR numeric columns in Google Sheets Query.

=QUERY({Sheet1!A1:AX,transpose(query(transpose(Sheet1!F1:AX),,9^9))}, "SELECT Col1 WHERE Col51 matches '.*\s90\s.*'",1)

This will return the names from column A if any of the columns from 6 to 50 matches the number 90.

To match the number 190 replace 90 with 190. The \s surrounding the number is to specify space characters.

That’s all.

Thanks for the stay. Enjoy!

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.

Generate Unique Random Numbers in Excel with Dynamic Arrays

Unique random numbers refer to random numbers without duplicates. Generating such numbers is straightforward...

Lookup Previous Values Dynamically in Excel and Google Sheets

Sometimes, you may want to look up the previous values relative to the current...

Sort Data but Keep Blank Rows in Excel and Google Sheets

Using a dynamic array formula or the FILTER command, we can sort data and...

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

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.