Search code examples
regexgoogle-sheetsgoogle-sheets-formulagoogle-sheets-querygoogle-query-language

Google Sheets query rows containing commas

I've a column Colors that contains rows of colors. I've a query I'm using that I want to include getting the colors based on a cell the user will input. For example the user will input Blue, Black and I want my query to return rows 2, 4, 5 along with the conditions in my query.

I'm stuck on how to do it as I would need to parse the commas in order to get each individual colors. I've tried creating a function in the query but that doesn't work and neither is creating a function that includes my whole query.

enter image description here

=QUERY('Sheet 1'!A1:D13, "select * where (AA contains 'Car' AND {*Return rows containing Blue and Black*})")

Edit: Sorry if I wasn't clear, I want to be able to capture user's input on D2 and include it in my query to return the rows that has "Car" in column AA and contains either or both of the colors "Blue" and "Black"

Solution

  • try:

    =QUERY({A2:B}, 
 "where lower(Col1) matches '.*"&REGEXREPLACE(LOWER(D1), ", ?", ".*|.*")&".*' 
    and 1=1")

    0