How to filter an excel range based on multiple dynamic filter conditions-CodePudding

I would like to filter a dataset considering several filter condition that can change dynamically based on a list of possible values. I have filter criteria team which have the following values: Team A, Team B, ALL, where ALL is a wildcard representing all teams. For the second criteria release, in case I want to include more than one release in the filter, the values are separated by comma, for example: Release: A, B, A,B, where the last one means to include both release A and B. I was able to make it works except for the case when the release value is A,B.

I think I should use: TRANSPOSE(TEXTSPLIT(B4,", ")) to obtain in a column the list of values and then in some how (maybe using MMULT) to be able to add the filter condition per each row, but I was not able to do it (in Excel, because using Google Spreadsheet I was able to do it using a query function)

The following formula for single release value works for both criteria:

FILTER(D3:H8, (IF(B3="ALL", D3:D8<>"*",D3:D8=B3)) * (E3:E8=B4))

but it doesn't work for the general case where release values are more than one.

I am looking for a solution that would not require a VBA script

Here is the sample file:

Here is a link to the sample excel file: (if you decide to have multiple releases delimited by a comma without the space then the SUBSTITUTE() function will require to be updated accordingly)

CodePudding user response：

Expanding the idea suggested by: @scottCraner, this would be the solution for the release condition:

ISNUMBER(MATCH(E3:E9,TRANSPOSE(TEXTSPLIT(B5,", ")),0))

so the entire formula will look like:

=FILTER(D3:H9, (IF(B3="ALL", D3:D9<>"*",D3:D9=B3)) * 
   (ISNUMBER(MATCH(E3:E9,TRANSPOSE(TEXTSPLIT(B4,", ")),0))))

Note: it works also removing the TRANSPOSE function, i.e.:

=FILTER(D3:H9, (IF(B3="ALL", D3:D9<>"*",D3:D9=B3)) * 
   (ISNUMBER(MATCH(E3:E9,TEXTSPLIT(B4,", "),0))))

Here is the sample solution: