# Google Sheets filter or query where range of columns matches condition

I feel like this should be simple but haven't been able to figure it out or find a corresponding question/answer.
I need to get specific column values for rows where any column in a range matches a value. I can do it with FILTER using OR (+) condition for every column, and had a similar solution with QUERY (using SELECT, WHERE and OR) as well. Wondering if there is any way I can just specify the range of columns without using 12+ OR conditions.

Ie. I need the columns B and C from rows in my 'MASTER SCHEDULE' sheet where any cell in columns D:O matches the value I have in cell A1. The below formula works, but I want to know if there is a way to shorten them.

`=FILTER('MASTER SCHEDULE'!B:C, ('MASTER SCHEDULE'!D:D = A1)+('MASTER SCHEDULE'!E:E = A1)+('MASTER SCHEDULE'!F:F = A1)+('MASTER SCHEDULE'!G:G = A1)+('MASTER SCHEDULE'!H:H = A1)+('MASTER SCHEDULE'!I:I = A1)+('MASTER SCHEDULE'!J:J = A1)+('MASTER SCHEDULE'!K:K = A1)+('MASTER SCHEDULE'!L:L = A1)+('MASTER SCHEDULE'!M:M = A1)+('MASTER SCHEDULE'!N:N = A1)+('MASTER SCHEDULE'!O:O = A1))`

So I am looking for something like this, or a similar query (or other) function:

`=FILTER('MASTER SCHEDULE'!B:C, ('MASTER SCHEDULE'!D:O = A1))`

A similar question was asked in Stackoverflow Google Sheets query return all rows if they match any value in a column on another sheet and the answer by @JPV suits your scenario.

Substitute this formula for your existing formula. Note, this formula will create a query output for all matching rows.

`=query(A1:L,"SELECT B, C WHERE A matches '"&TEXTJOIN("|", 1, D1:L)&"' ",1)`

In the screenshots below, the raw data is identical but note how a change in a value in Column A in "V2" results in an additional match.

Sample data v1

Sample data V2

• This looks like it will get rows where A matches any value in D through O in row 1. While what I need is a formula which will get rows where any value in the D to O columns matches A1 (ie. a specific value). So I would need a textjoin for each row, not just D1:O1... and I don't see a way to do that (without a very long and repetitive formula, again). Commented Mar 29, 2021 at 18:36
• Apologies - confusion reigned! Updated the answer and images included to show how additional matches are created by a change in a value in Column A. Commented Mar 29, 2021 at 23:06