I have data on my Sample_Data tab that I would like to query by using a multi-select dropdown. Sample_Search_Form is the name of the tab where I am trying to filter my results. The multi-select dropdown in Sample_Search_Form!B4 has values that could appear in Col3 or Col4. I would like my query logic to be "select all columns where the values that are selected in the dropdown occur at least once in Col3 or Col4.
Here is the data on the Sample_Data tab:
| Original Data | |||
|---|---|---|---|
| Timestamp | Sample Data | Assigned by Dept 1 | Assigned by Dept 2 |
| 1/26/2025 11:59:38 | Project 1 | Group A | Group A |
| 1/27/2025 10:46:45 | Project 2 | Group B | |
| 1/28/2025 8:52:53 | Project 3 | Group A, Group C | Group B |
| 1/28/2025 9:44:03 | Project 4 | Group C, Group A |
For example:
If my filter in Sample_Search_Form!B4 contains Group A: the data for Project 1, Project 3, and Project 4 should be displayed.
If my filter in Sample_Search_Form!B4 contains Group B: the data for Project 2 and Project 3 should be displayed.
If my filter in Sample_Search_Form!B4 contains Group A, Group B: the data for Project 3 should be displayed.
If my filter in Sample_Search_Form!B4 contains Group A, Group C: the data for Project 3 and 4 should be displayed.
Here is the formula I tried:
=QUERY(Sample_Data!A9:D,"Select Col1,Col2,Col3,Col4 where Col1 is not null "
& if(B4<>"", "and Col3 matches '"&TEXTJOIN("|",TRUE,SPLIT(B4,",",FALSE,TRUE))&"' or Col4 matches '"&TEXTJOIN("|",TRUE,SPLIT(B4,",",FALSE,TRUE))&"'",""))
This had a lot of errors, so I tried:
=QUERY(Sample_Data!A9:D,"Select Col1,Col2,Col3,Col4 where Col1 is not null "
& if(B4<>"", "and Col3 matches '.*"&TEXTJOIN("|",TRUE,SPLIT(B4,",",FALSE,TRUE))&".*' or Col4 matches '.*"&TEXTJOIN("|",TRUE,SPLIT(B4,",",FALSE,TRUE))&".*'",""))
The results were closer but still not correct. Is there any way to change the second parameter of the query to display the correct results? A sample anonymous sheet can be found here.
Note in regard to z.'s comment:
The above is a sample as I am trying to figure out how to do a query for this particular use case. The actual formula I am using is quite a bit more complicated:
=IFERROR(vstack( { "", "", "", "", "", "", "" , "" , "" , "" , "" , "" , "" , "" , "" , "" },
IFERROR(SORT(QUERY(ARRAYFORMULA( REDUCE( TOCOL(,1), TOCOL(Sheet_Names!C3:C,1),
LAMBDA(a,c,VSTACK(a,IFERROR(INDIRECT(c)))))),
"Select Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col11,Col12,Col13,Col14,Col15,Col17,Col10 where Col1 <>''"
& if(F5<>"", "and Col19 = '"&F5&"'", "")
& if(H5<>"", "and Col11 = '"&H5&"'", "")
& if(J5<>"", "and Col30 matches'"&TEXTJOIN("|",TRUE,SPLIT(J5,", ",FALSE))&"'", "")
& if(L5<>"", "and Col13 = '"&L5&"'", "")
& if(O5<>"", "and Col10 matches '.*"&TEXTJOIN("|",TRUE,SPLIT(O5,", ",FALSE))&".*' or Col18 matches '.*"&TEXTJOIN("|",TRUE,SPLIT(O5,", ",FALSE))&".*'", "")),8,TRUE,9,TRUE),)),"—")
This displays data based on multiple filters. It takes the array of sheet names on Sheet_Names!C3 and displays all results from each sheet based on the filters. This is the line I am having trouble with:
& if(O5<>"", "and Col10 matches '.*"&TEXTJOIN("|",TRUE,SPLIT(O5,", ",FALSE))&".*' or Col18 matches '.*"&TEXTJOIN("|",TRUE,SPLIT(O5,", ",FALSE))&".*'"
and the question was phrased to address this issue.
Try
=ARRAYFORMULA(QUERY(
HSTACK(
Sample_Data!A9:D,
Sample_Data!C9:C & "," & Sample_Data!D9:D
),
"SELECT Col1, Col2, Col3, Col4
WHERE 0=0" &
IF(B4="",," and Col5 contains '" & JOIN("' and Col5 contains '", SPLIT(B4, ", ", )) & "'")
))