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

[AJPatel73]

0
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.

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:

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

 

[Peter_SSs]

Welcome to the MrExcel board!

For the future, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

Is this what you want?

25 03 06.xlsm
	A	B	C
1	Status	Supply	Company
2	Active	Nuts Bolts	ABC
3	Active	Nuts	DEF
4	Inactive	Washers	GHI
5	Active	Screws Washers	JKL
6	Inactive	Nuts	MNO
7
8
9	Supply	# of Companies	Companies
10	Nuts	2	ABC
11			DEF
12
Supply

Cell Formulas
Range	Formula
B10	B10	=COUNTIFS(B2:B6,"*"&A10&"*",A2:A6,"Active")
C10:C11	C10	=FILTER(C2:C6,(A2:A6="Active")*ISNUMBER(SEARCH(A10,B2:B6)),"")
Dynamic array formulas.