How to filter with user-input data? [Google Sheets]

I have a question about dynamic filtering.

Suppose I have a number of services and their providers, and I'm trying to filter by a particular criteria. Easy enough.

But suppose that I wasn't the one generating the criteria? How do I code a filter to show results when I don't know what column we're checking again?

See my spreadsheet (permissions open) for more explanation https://docs.google.com/spreadsheets/d/1Bj_OGyCyobXmoFq72M-v4lcNiGfrgi1AX5QeeZIi-FY/edit?usp=sharing

Solution 1:

Here's how I would approach this

=filter(B4:B6,filter(C4:D6,C3:D3=B22)="Y")

Or you can do it by unpovoting the data and running a query()

=ArrayFormula(query(split(flatten(B4:B6&"❄️"&C3:D3&"❄️"&C4:D6),"❄️"),"select Col1 where Col2='"&B22&"' and Col3='Y'"))

Another way to dynamically detect the column/row is with indirect(R1C1)-match

=filter(B4:B6,indirect("R4C"&match(B22,3:3,0)&":R6C"&match(B22,3:3,0),0)="Y")