Google Sheets: Filtering based on split strings

Question

I'm trying to create a filtering sheet where I can enter multiple words in a single cell, delimited with a comma, and return any row that contains any of those terms. I've created an example spreadsheet.

The problem I'm having is that I need to split the input data (green cell A2) into individual values, split the data to search (yellow cells G3:G12), and return any rows that have any of those terms, with no regard for the order of the values in either part, while also returning based on partial terms.

In the example, the search terms "Bl, Green, Red" should return any row with "Black" "Blue" "Green" or "Red" in their list of colors.

So far I've been able to create a complex filter that uses numerical ranges, checkboxes, an comparisons with Regex for other types of filtering, but this one is stumping me since there are multiple values in a single cell, and sheets does not have an IFCONATINS(). If I can figure this out, I'd like to also allow for the option of searching for "Any" or "All" of the terms, to make the search more general or specific.

I've tried various combinations of SPLIT(), SEARCH(), and ARRAYFORMULA(), but with no luck.

Answer 1

You may try:

=FILTER(G3:G12, REGEXMATCH(G3:G12,SUBSTITUTE(A2,", ","|")))