regexgoogle-sheetsfilterarray-formulas

Google Sheets Filter on REGEXMATCH AND Condition


I have a spreadsheet with multiple tabs in which I would like to filter all existing rows in one sheet (the Source sheet) based on a set of comma-separated tags.

I have a dropdown at the top of the column in the View sheet in which you can select one or more tags from the set of all comma-separated tags in the Source sheet's. Currently, I am able to easily perform an OR operation by using FILTER with REGEXMATCH, making use of the | Regex operator.

REGEXMATCH('Source'!$N$3:$N, JOIN("|",ARRAYFORMULA(TRIM( SPLIT('View'!$N$3,",") ))) )

As stated, this is an OR operation - adding additional tags to the filter (Cell 'View'!$N$3, a dropdown selector) will expand the results, rather than trim them.

I would like to set this up such that it performs an AND operation, rather than an OR operation. I thought I could try using a positive lookahead, but it doesn't seem like lookaheads are supported by google sheets.

An AND could maybe work across a SPLIT, but using ARRAYFORMULA to surround the REGEXMATCH causes the Source range to flatten as well, which breaks the FILTER.

AND(ARRAYFORMULA(REGEXMATCH('Source'!$N$3:$N, ARRAYFORMULA(TRIM( SPLIT('View'!$N$3,",") )) )))

What would be the suggested method of implementing this? The lines shown are a single filter parameter of a large FILTER method.

Example

For additional context, lets say that 'Source'!$M$3:$M' is a "Name" column containing people's names, and 'Source'!$N$3:$N is a "Hobbies" column, containing comma-separated lists of hobbies. We'll say that John likes Baking, Smith likes Basketball, and Peter likes both Baking, and Basketball (so that 'Source'!N5 equals "Baking, Basketball').

Names Hobbies
John Baking
Smith Basketball, Ballooning
Peter Baking, Basketball, Ballooning

Edit: Added "Ballooning" to Smith and Peter, since I would like my search to continue to ignore any unrelated tags.

If I set 'View'!$N$3 to "Baking, Basketball", my FILTER on the range 'Source'!$M$3:$M currently outputs three rows, containing "John", "Smith", and "Peter".

Names
John
Smith
Peter

I would like it to instead output only "Peter". It should also output the same if I instead put "Basketball, Baking", and should not be limited to only two values for 'View'!N3.

Names
Peter

I am also retrieving more than 1 column from the dataset - I am only using one for the example to keep it simple.


Solution

  • Different approach, doing the and outside the regexmatch on the individual View items by applying it byrow:

    =filter(tocol(Source!M3:M,1),byrow(tocol(Source!N3:N,1),lambda(row,and(regexmatch(row,split(View!N3,", ",1,1))))))
    

    N.B. As per Tom Sharpe's comment to the previous answer, this formula would still give the answer 'Peter' for a View of 'Baking, Basketball' if the Source row contained 'Baking, Basketball, Ballooning'