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

[onlyforsubu]

Purple! Pink! Gold! Nude! Orange! Yellow! Brown

Ivory! Black! Green! Silver! Purple! Pink! Nude

Purple! Pink! Gold! Nude! Orange! Yellow! Brown

<tbody>
</tbody>

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

Red	17
White	18
Ivory	82
Blue	19
Black	20
Green	24
Silver	21
Purple	23
Pink	22
Gold	25
Nude	81
Orange	83
Yellow	90
Brown	91

<tbody>
</tbody>

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.