FILTER() function where values exist in a range-CodePudding

Let "serviceRendered" named range be a column of values where there are multiple "services" inside the cell separated by commas. Example: "Bus passes, Food Service" OR "Food Service, Action Planning" etc.

Let "ListOfServices" be a named range that contains each service down the column. So A2 = "Bus Passes", A3 = "Food Service" etc.

This is the current formula.

=filter(serviceRendered, serviceRendered <> "", serviceRendered <> "N/A")

But somewhere in that formula I want to add:

, serviceRendered exists in ListOfServices.

To be even more specific, I want each word inside the cells in serviceRendered to EXIST inside the listOfServices range. So if there is a cell inside serviceRendered that says "Food Service, Bus Passes", I want the formula to pull that because "Food Service" and "Bus Passes" exist in the listOfServices range.

CodePudding user response：

try:

=FILTER(serviceRendered, 
        serviceRendered <> "", 
        serviceRendered <> "N/A", 
        REGEXMATCH(LOWER(serviceRendered), TEXTJOIN("|", 1, LOWER(ListOfServices))))

CodePudding user response：

If I'm understanding the problem correct (which is admittedly difficult without any spreadsheet, data or layout to look at), this may be what you're after:

=ArrayFormula(FILTER(serviceRendered,MMULT(1*NOT(ISERROR(VLOOKUP(TRIM(SPLIT(serviceRendered,",")),ListOfServices,1,FALSE))),SEQUENCE(COLUMNS(SPLIT(serviceRendered,",")),1,1,0))=LEN(REGEXREPLACE(serviceRendered,"[^,]","")) 1))