Google Sheets QUERY to return if contains multiple selection and all other values

Question

I have a data set similar to the image in cells A5:D31. I am trying to write a QUERY function that will return the records that match the "filters" in range A2:D3.

In this example, the query will return the rows highlighted in green WHERE Department = Furniture OR Electronics AND Size = Small. The other columns 'Color' and 'Store' are not specified so any value will return if the cell is not blank.

Answer 1

This is basically an exercise in building your query text string. For simplicity, the solution puts parentheses around text that doesn't actually need it.

    For example, it returns

"Where (B = 'Furniture' OR B = 'Electronics') AND (C = 'Small')"

    instead of

"Where (B = 'Furniture' OR B = 'Electronics') AND C = 'Small'"

Formula

=LET(
   range,A5:D, dp,{B2;"B"}, sz,{D2;"C"}, cl,{B3;"A"}, 
   st,{D3;"D"}, nums,"D",
   querytxt,BYCOL({dp,sz,cl,st}, LAMBDA(x, LET(
     v,INDEX(x,1), c,INDEX(x,2), q,IF(ISERR(FIND(c,nums)),"'",), 
     IF(OR(v="All", LEN(TRIM(v))=0),, "("& 
       JOIN(" OR ", ARRAYFORMULA(c& " = "& q& 
       TRIM(SPLIT(v, ","))& q))& ")")))),
   QUERY(range, IF(LEN(JOIN(,querytxt)), 
     "Where "& JOIN(" AND ",TOCOL(querytxt, 1)),),1))

Explanation

1. LET is used to store values and calculations for later use.
2. The initial variables define
   1. range of your data including headings
   2. dp, sz, cl, and st are two-row arrays that each store a reference to filter values for a column (in row 1) and the actual column they should be applied to (in row 2).
      • For example, dp contains B2 in row 1, which is a reference to a cell containing filter value(s), and "B" in row 2 which is the column in range that those filter values should be applied to.
      • the labels follow the naming of your headings dp=department, st=store etc. but that is largely irrelevant and the arrays can be swapped between variables without causing issues.
   3. Lastly nums is a string that identifies columns that contain numbers rather than strings. In this case only column D. If the Size column C also contained numbers, then you'd add C to that string. For example nums,"CD", or nums,"DC",. If you have columns higher than Z then the approach needs to be tweaked.
3. querytxt stores an array of values. Each value represents the filter string for one column or is empty. The array is created as follows:
   1. BYCOL is used to pass each of the filter variables into a LAMBDA function one-by-one, and the LAMBDA stores the current column in the variable x.
   2. The LAMDA's formula uses another LET function to create three additional variables:
      1. v stores the first row of x (the filter values)
      2. c stores the second row of x (the column letter)
      3. q stores nothing if c is in nums, otherwise it stores a single quote '. q is used to ensure proper syntax for number vs. text columns.
   3. The formula next checks if v="All" or no value, since filtering by everything is equivalent to filtering by nothing, and returns nothing further if TRUE.
      • LEN is used to test for characters in v after first applying TRIM so that a cell containing only spaces is treated as empty.
   4. If there is a string in v that isn't "All", then the formula creates a string made up of:
      1. An array of values is created within an ARRAYFORMULA by:
         1. Splitting v on , with TRIM removing any spaces padding the values: TRIM(SPLIT(v, ","))
         2. Each value in the array from SPLIT has c & " = " & q prepended, and q appended. For example if v="Furniture, Electronics" and c="B" and q="'" then the result would be:

            {"B = 'Furniture'", "B = 'Electronics'"}
            

      2. The array of values is joined with " OR " then placed within parenthesis () and that is the string returned for the current x.
         • For example, using the previous example where x={B2;"B"}={"Furniture, Electronics","B"}, therefore v="Furniture, Electronics", c="B" and q="'" the formula would return the following for x:

           "(B = 'Furniture' OR B = 'Electronics')"
           

4. Finally the QUERY function is created using the values from querytxt. Using the example values, it would return the result of:
   

   =QUERY(range, "Where (B = 'Furniture' OR B = 'Electronics') AND (C = 'Small')",1)
   

   This is achieved by:
   1. IF is used to return an empty value if querytxt contains no filters (equivalent to all column filters set to "All" or blank).
   2. If there are filters then TOCOL is used to remove any empty values and then querytxt is joined using " AND " and "Where " is prepended.

Answer 2

Alternative approach using matches rather than booleans to handle multiple wheres per column (as we can concatenate the terms with |):

=arrayformula(let(
cols,{"A";"B";"C";"D"},
filtcells,{"B3";"B2";"D2";"D3"},
regex_,lambda(x,if(x="all","'.*'","'"&join("|",trim(split(x,",")))&"'")),
string,"where "&join(" and ",map(cols,filtcells,lambda(eachcol,eachcell,eachcol&" matches "&regex_(indirect(eachcell))))),
query(A5:D,string,1)))

N.B. when the relevant filter cell is set to 'all', a '.*' regex is used (i.e. any number of any character) to return all rows.