Return multiple values on comparison

[cathyk1980]

Hi there. I have 3 columns of data. Column B is a comma separated value in each cell. I need to search column B for each value in Column C and return the corresponding value in Column A. So for the example below, I would search 14-ESDV-7011 and return 3 values from column A, preferably in one cell (D1) comma separated Here is an example of my data:

A	B	C
WR-H-12-J-LD-76530-001	12-SXY-7653, 12-JB-7655, 64-J-8040 <tbody> </tbody>	14-ESDV-7011
WR-H-14-J-LD-70110-002 <tbody> </tbody>	14-ESDV-7011, 14-ZSC-7011, 59-J-7374, 64-J-8040, GJB-AU <tbody> </tbody>	17-PIT-7051 <tbody> </tbody>
WR-H-17-B-PI-00003-001 <tbody> </tbody>	14-ESDV-7011, 15-P-7261, 16-HOV-7463, 17-ED-7075, 17-FCV-7055, 17-FCV-7060, 17-FCV-7065, 17-FCV-7070, 17-HOV-7422, 17-HOV-7423, 17-HOV-7424, 17-HOV-7431, 17-HOV-7432, 17-HOV-7433, 17-HOV-7435, 17-HOV-7436, 17-HOV-7440, 17-HOV-7441, 17-HOV-7442, 17-HOV-7443, 17-HOV-7444, 17-HOV-7445, 17-HOV-7446, 17-HOV-7447, 17-HOV-7448 <tbody> </tbody>	17-PIT-7051 <tbody> </tbody>
WR-H-14-J-LD-70110-001 <tbody> </tbody>	14-ESDV-7011, 59-J-7374, 64-J-8040, GJB-AU <tbody> </tbody>	17-PIT-7850 <tbody> </tbody>
WR-H-17-J-LD-74850-001 <tbody> </tbody>	14-JB-7361, 17-PT-7485, 59-J-7374, 64-J-8040, GJB-AU <tbody> </tbody>	17-PT-7485 <tbody> </tbody>

<tbody>
</tbody>

Any help would be greatly appreciated as there are ~1800 values to search.

Thanks!
Cathy

 

[DRSteele]

Welcome to the Forum

You require Excel 2016 to use the new function TEXTJOIN.

In D1, put this and copy it down as far as required.:

	D
1	WR-H-14-J-LD-70110-002, WR-H-17-B-PI-00003-001, WR-H-14-J-LD-70110-001

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet34​

Array Formulas Cell Formula D1 {=TEXTJOIN(", ",,IF(IFERROR(SEARCH(C1,$B$1:$B$5),0),$A$1:$A$5,""))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try to enter the {} manually yourself

<tbody>
</tbody>

 

[cathyk1980]

Thank you for that! Worked wonderful and saved me so much time !

 

[DRSteele]

You're welcome. Great to hear!