0

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))

0

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 Before


Sample data V2 after

2
  • 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). – sachernik Mar 29 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. – Tedinoz Mar 29 at 23:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.