[SOLVED] Using the filter function to return all instances in which criteria matches two other cells, one of which has multiple lines of data

Using the filter function to return all instances in which criteria matches two other cells, one of which has multiple lines of data

I have seen many posts for using the filter function for multiple criteria and the solutions works great if both cells that you are looking for a match in only has 1 line of data. I have a spreadsheet where one of the columns in which you are looking for a match has cells that have multiple lines of data separated by carriage return within the cell. I need to be able to see if what I am looking for is within that cell so that the data from another column is returned.

Table

As you can see on the attached table, some cells in column B have multiple lines of data. I created a summary tab that looks like the following attached image with the expected results:

Results

Column A2 in the Results attached is a drop down list where you can select the type of supply

I have a countif statement In B2 that includes a wild card so I get the total # of companies that are active that supply Nuts (this is a cell reference to A2) which in this case is 2

Column C I want to list out the active companies that supply nuts, and that is where I run into issue when I have a company that supplies multiple products (listed in the same cell) in column B of the data table

Amy help you can provide or direct me to where I can get assistance is greatly appreciated

Solution

Filter Using Dropdowns with Dynamic Array Formula

In E1:
```
=IF(E2="",0,ROWS(E2#))
```

In E2:

=LET(sst,Sheet1!A2:A6,ssu,Sheet1!B2:B6,sco,Sheet1!C2:C6,
        dst,B2,dsu,B1,dlm,CHAR(10),
    fst,IF(dst="",EXPAND(1,ROWS(sst),,1),sst=dst),
    fsu,IF(dsu="",EXPAND(1,ROWS(ssu),,1),
        ISNUMBER(SEARCH(dlm&dsu&dlm,dlm&ssu&dlm))),
    FILTER(sco,fst*fsu,""))