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.

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.

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.

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!