Lookup and return multiple text values, approximate match

[northw]

Hello,
I've been tinkering with XLOOKUP function to bring up multiple values but I've not had much success. I need to lookup codes (fruit in the example below), which can have multiple values (all in the same column, but different cells), which need to be consolidated to a single cell and comma seperated. And to complicate things, values have other characters before/after. Any help is appreciated.

For example:

Fruit	Image		Fruit	Images
Apple	XyzBerry01		Apple	ImgApple01, ImgApple02,ImgApple03
Berry	ImgApple01		Berry	XyzBerry01,XYzBerry02
Orange	ImgApple02		Orange	AbcOrange001,AxzOrange002
Pear	ImgApple03		Mango	NNMango01
Mango	XYzBerry02
	AbcOrange001
	AxzOrange002
	NNMango01

Thank you!

 

[Rick Rothstein]

For your copy of XL365, give this formula a try...

=LET(g,HSTACK(A2:A6,MAP(A2:A6,LAMBDA(x,TEXTJOIN(", ",,FILTER(B2:B9,ISNUMBER(SEARCH(x,B2:B9))))))),FILTER(g,NOT(ISERROR(TAKE(g,,-1)))))

 

[northw]

I won't even try to understand this formula, but all I can say is that, it works! Thank you!