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

c.clavin

Board Regular
Joined
Mar 22, 2011
Messages
130
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
ABCDE
1Part NumberBrand
25050-01 5050-02FordKeyResult
35050-01 5050-02Chevrolet5051-02Ford, Chevrolet
45051-01 5051-02Dodge
Sheet1
 
Why do you expect the result to be Ford and Chevrolet? I would expect Dodge only for 5051-02.
 
Upvote 0
Try:
Book4
ABCDE
1Part NumberBrandKeyResult
25050-01 5050-02Ford5051-02Dodge
35050-01 5050-02Chevrolet5050-02Ford, Chevrolet
45051-01 5051-02Dodge
Sheet8
Cell Formulas
RangeFormula
E2:E3E2=TEXTJOIN(", ",,FILTER($B$2:$B$4,ISNUMBER(SEARCH(D2,$A$2:$A$4)),""))
 
Upvote 0
Solution
Wow that is amazing, I was not thinking about the SEARCH function thank you so much :)
 
Upvote 0

Forum statistics

Threads
1,226,835
Messages
6,193,240
Members
453,783
Latest member
Chandni

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top