Search For String In Column And Return Result From Another Column

[excelques2391]

Hello! I have 2 data sets:

Dataset1:

ContainerName	Collection
ContainerA	FRUITS\Apple, FRUITS\banana, FRUITS\GRAPES
ContainerB	FRUITS\Pears, FRUITS\oranges
ContainerC	FRUITS\Apple
ContainerD	FRUITS\banana

Dataset2:

Fruit
apple
BANANA
grapes
pears

For each row in Dataset2, I am looking to search in the Collection column of Dataset1 and return all of the values where a specific fruit exists. For example, from Dataset2, "apple" should return "ContainerA, ContainerC".

I tried xlookup, and while I was able to get the first result, I wasn't able to get all of the results.

In looking at the Filter function, it appears that it doesn't support wildcards, which I believe I need to account for the commas and the prefix "FRUITS\".

Is there any way to construct an Excel formula to extract this information?

Thanks!

 

[DRSteele]

Welcome to the Forum!

There are probably better ways, but I think this will work. Note that none of this is case-sensitive.

MrExcel posts19.xlsx
	C	D
5	ContainerName	Collection
6	ContainerA	FRUITS\Apple, FRUITS\banana, FRUITS\GRAPES
7	ContainerB	FRUITS\Pears, FRUITS\oranges
8	ContainerC	FRUITS\Apple
9	ContainerD	FRUITS\banana
10
11	fruit	containers
12	apple	ContainerA, ContainerC
13	BANANA	ContainerA, ContainerD
14	grapes	ContainerA
15	pears	ContainerB
Sheet27

Cell Formulas
Range	Formula
D12:D15	D12	=TEXTJOIN(", ",,IFERROR(IF(SEARCH(C12,$D$6:$D$9),$C$6:$C$9),""))

 

[excelques2391]

Thank you very much - that did the trick!

 

[DRSteele]

Awesome! You're welcome.