Lookup multiple values in a single cell (separated by commas) and then return the values to a single cell (also comma separated)

[onlyforsubu]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Purple! Pink! Gold! Nude! Orange! Yellow! Brown[/TD]
[/TR]
[TR]
[TD]Ivory! Black! Green! Silver! Purple! Pink! Nude[/TD]
[/TR]
[TR]
[TD]Purple! Pink! Gold! Nude! Orange! Yellow! Brown[/TD]
[/TR]
</tbody>[/TABLE]

Above are values & we have another sheet of categories. In categories there are numbers like below

[TABLE="width: 330"]
<tbody>[TR]
[TD="align: center"]Red[/TD]
[TD="align: center"]17[/TD]
[/TR]
[TR]
[TD="align: center"]White[/TD]
[TD="align: center"]18[/TD]
[/TR]
[TR]
[TD="align: center"]Ivory[/TD]
[TD="align: center"]82[/TD]
[/TR]
[TR]
[TD="align: center"]Blue[/TD]
[TD="align: center"]19[/TD]
[/TR]
[TR]
[TD="align: center"]Black[/TD]
[TD="align: center"]20[/TD]
[/TR]
[TR]
[TD="align: center"]Green[/TD]
[TD="align: center"]24[/TD]
[/TR]
[TR]
[TD="align: center"]Silver[/TD]
[TD="align: center"]21[/TD]
[/TR]
[TR]
[TD="align: center"]Purple[/TD]
[TD="align: center"]23[/TD]
[/TR]
[TR]
[TD="align: center"]Pink[/TD]
[TD="align: center"]22[/TD]
[/TR]
[TR]
[TD="align: center"]Gold[/TD]
[TD="align: center"]25[/TD]
[/TR]
[TR]
[TD="align: center"]Nude[/TD]
[TD="align: center"]81[/TD]
[/TR]
[TR]
[TD="align: center"]Orange[/TD]
[TD="align: center"]83[/TD]
[/TR]
[TR]
[TD="align: center"]Yellow[/TD]
[TD="align: center"]90[/TD]
[/TR]
[TR]
[TD="align: center"]Brown[/TD]
[TD="align: center"]91
[/TD]
[/TR]
</tbody>[/TABLE]

We want above colors in numbers like

17,22,25........ etc. in same cell

Can someone help me please ????

 

[J.Ty.]

Hi,

Welcome to MrExcel Forum!

To be honest, the idea of storing multiple pieces of information in a single cell is unfortunate. Sooner or later it causes problems, which you are now experiencing: your task is quite easy when the color names are located in spearate cells, and difficult otherwise. Many other problmes may surface later.

What is the reason for keeping all the names together?


J.Ty.