Search code examples
google-sheetsgoogle-sheets-query

Google sheets - query cell value contains value in comma separated cell

I'm trying to query a group of say fruits so I'm grouped these into a comma-separated cell and I want to query that cell but I don't get any results. When I just one value in that cell says Apple it wants a result. I thought using contains would allow me to do that in a query? If not are they better methods?

Table

A B
Bob Apple
May Orange
Simon Apple
Sam Pear
Tom Grape

Query

=IFERROR(QUERY('Sheet1'!$A2:$AB500, "select A,C,Y where D contains 'Staff' and (K='Y' or L='Y') and C contains '"&G7&"' ", 0),"None")

G7 contains 'Apple,Pear,Grape,Orange'

Solution

  • Try changing this part

    C contains '"&G7&"'

    to

    C matches '"&SUBSTITUTE(G7, ",", "|")&"'

    and see if that helps?

    EDIT: if you have brackets in the range and in G7 try

     =ArrayFormula(IFERROR(QUERY({A:C, regexreplace(B:B, "\(|\)",)}, "select Col1 where Col3 contains 'Team' and Col4 matches '"&SUBSTITUTE(REGEXREPLACE(D2, "\(|\)",), ",", "|")&"' ", 0),"None"))

    and see if that works?