Using XLOOKUP or INDEX/MATCH to search multiple words in cell?

[c.clavin]

Hi guys, I'm very deep in a sheet and for dumb reasons I needed to put 2 different part numbers in the same cell. Is there a way to XLOOKUP or INDEX/MATCH one cell to a specific 'word' in another cell and spit it out like in the red? I am so lost on this. Thank you!

Book1
	A	B	C	D	E
1	Part Number	Brand
2	5050-01 5050-02	Ford		Key	Result
3	5050-01 5050-02	Chevrolet		5051-02	Ford, Chevrolet
4	5051-01 5051-02	Dodge
Sheet1

 

[Cubist]

Why do you expect the result to be Ford and Chevrolet? I would expect Dodge only for 5051-02.

 

[c.clavin]

you are correct, sorry I mistyped 5050-01 as 5051-02.

 

[Cubist]

Try:

Book4
	A	B	C	D	E
1	Part Number	Brand		Key	Result
2	5050-01 5050-02	Ford		5051-02	Dodge
3	5050-01 5050-02	Chevrolet		5050-02	Ford, Chevrolet
4	5051-01 5051-02	Dodge
Sheet8

Cell Formulas
Range	Formula
E2:E3	E2	=TEXTJOIN(", ",,FILTER($B$2:$B$4,ISNUMBER(SEARCH(D2,$A$2:$A$4)),""))

 

[c.clavin]

Wow that is amazing, I was not thinking about the SEARCH function thank you so much

 

[Cubist]

You're welcome.