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