Filter an excel range based on multiple dynamic filter conditions (with column values delimited)

Question

I posted a similar question: Filter an excel range based on multiple dynamic filter conditions. Now I am considering a more general case, i.e. for one of the filter column (Releases, column E) it may have several values delimited by comma. The expected result should filter by rows that have as release values: A or B, but the releases column can come with more than one value and for team filter by specific one or all of them (ALL wildcard).

Here is the sample (when we have a maximum of two values for releases column):

I was able to obtain the desired result based on filter conditions, but it requires helper columns (columns: J,K,L), via the formula in N3:

=FILTER(D3:H15, (IF(B3="ALL", D3:D15<>"*",D3:D15=B3)) * (L3:L15))

and column L does the magic to identify the rows with the wanted release values:

=LET(result, ISNUMBER(MATCH(J3:K15,TEXTSPLIT(B4,", "),0)), IF((FILTER(result, {1,0}) 
+ FILTER(result, {0,1}))>0, TRUE, FALSE))

I am looking for a solution that wouldn't require helper columns and also for the general case where Release column can have more than two values, for example: A, C, G, F... if that is possible.

Here a link to my sample file:

https://1drv.ms/x/s!AlZxw2GG3C7Ihyyx8_AM5ylbZWaI?e=F3WUep

Note:

1. I cannot use TEXTSPLITin a single invocation to obtain columns J,K, because when the text input argument is an array (range) there is no way to delimit by empty string, so TEXTSPLIT(E3:E15,",") doesn't return two columns (it works for a single cell, but not for a range), so I have to use TEXTAFTER(E3:E15,",") to obtain the information after the comma in column K

Answer 1

Lets try-

=FILTER(D3:H15,BYROW(E3:E15,
  LAMBDA(x,MAX(--ISNUMBER(XMATCH(TOCOL(TEXTSPLIT(x,",")),
  TOCOL(TEXTSPLIT(B4,", ")),0)))))
 * IF(B3="ALL",D3:D15<>"",D3:D15=B3))

Explanation of the solution to identify if release value is present:

It uses BYROW function which processes each row by a LAMBDA function you define.

The formula: TOCOL(TEXTSPLIT(B4,", ")

Generates a column array with the values of B4, i.e.: {A;B}(semicolon represents a column array) in our case a 2x1 array. TEXTSPLIT spits a string by delimiter (", ").

The formula: TOCOL(TEXTSPLIT(x,", "))

Generates an array column for a value represented by x split by the delimiter (", ") . For example if x is: A it will generate: {A} and for A,C the output will be: {A;C}, i.e 2x1 array.

The XMATCH function with signature: XMATCH(lookup_value, lookup_array, 0)

will return the index position of lookup_array when an exact match is found for look_value, otherwise N/A. If lookup_value is a column array, the XMATCH function is evaluated for each element of the array and return the result in a column array.

For lookup_array: {A;B} it will produce the following output, based on the following input values:

Lookup_value	Result
{A}	{1}
{A;C}	{1;N/A}
{C;D}	{N/A;N/A}
{A;B}	{1;2}
{B;A}	{2;1}
{B;A;C}	{2;1;N/A}
{C}	{N/A}

In our case:

XMATCH(TOCOL(TEXTSPLIT(x,", ")),TOCOL(TEXTSPLIT(B4,", ")),0)

will return for each releases value (x) ({A}, {A;B}, {A;C}, etc.) a column array of size of the number or elements of x, indicating the row position of {A, B} (if matches) or N/A (not found) for each element of x.

ISNUMBER converts the result to TRUE (if matches) or FALSE (for N/A). --ISNUMBER(cell) converts the result to 1 (match) or 0 (for N/A). Finally MAX function returns 1 if there is at least one match, otherwise 0.

Because BYROW processes the LAMBDA function for each row, it returns 1 (at least one match) or 0 (no match) for each row of E3:E15.

=BYROW(E3:E15,LAMBDA(x,
MAX(--ISNUMBER(XMATCH(TOCOL(TEXTSPLIT(x,", ")),
TOCOL(TEXTSPLIT(B4,", ")),0)))))

which is what we need as a filter condition

Note: You can use MATCH function instead of XMATCH, but keep in mind that for the third input argument the default behavior is different. The default value for MATCH is 1 (largest value that is less than or equal to lookup_value) and for XMATCH is 0 (exact match).