Find value within a cell with multiple text separated by comma

[Bav_2030]

Hi,

I have a column which contains several text separated by commas. I'm trying to see if it would be possible to have a formula which looks at the values in-between
the commas and if that value is found on sheet 2 it will display its corresponding value from column two

I don't even know if this would be possible to achieve but thought to ask in case someone has any pointers or tips to help with this - many thanks in advance.

Row	List of items	Expected result
2	Book, Bag, Hat	Blue, Red, Yellow
3	Hat	Yellow
4	Gloves, Bag	Black, Red
5	Pen, Marker, Hat	Pink, Orange, Yellow
6	Marker	Orange
7	Bag	Red
8	Book, Gloves	Blue
9	Ruler	White
10	Stamp	Green

Sheet 2

Item	Colour
Book	Blue
Bag	Red
Hat	Yellow
Gloves	Black
Marker	Orange
Ruler	White
Stamp	Green
Pen	Pink

 

[Fluff]

How about

Fluff.xlsm
	A	B
1	List of items	Expected result
2	Book, Bag, Hat	Blue, Red, Yellow
3	Hat	Yellow
4	Gloves, Bag	Black, Red
5	Pen, Marker, Hat	Pink, Orange, Yellow
6	Marker	Orange
7	Bag	Red
8	Book, Gloves	Blue, Black
9	Ruler	White
10	Stamp	Green
11
12
13
14
15	Item	Colour
16	Book	Blue
17	Bag	Red
18	Hat	Yellow
19	Gloves	Black
20	Marker	Orange
21	Ruler	White
22	Stamp	Green
23	Pen	Pink
24
Lists

Cell Formulas
Range	Formula
B2:B10	B2	=TEXTJOIN(", ",,VLOOKUP(TRIM(TEXTSPLIT(A2,",")),$A$16:$B$23,2,0))

 

[Bav_2030]

How about

Fluff.xlsm
	A	B
1	List of items	Expected result
2	Book, Bag, Hat	Blue, Red, Yellow
3	Hat	Yellow
4	Gloves, Bag	Black, Red
5	Pen, Marker, Hat	Pink, Orange, Yellow
6	Marker	Orange
7	Bag	Red
8	Book, Gloves	Blue, Black
9	Ruler	White
10	Stamp	Green
11
12
13
14
15	Item	Colour
16	Book	Blue
17	Bag	Red
18	Hat	Yellow
19	Gloves	Black
20	Marker	Orange
21	Ruler	White
22	Stamp	Green
23	Pen	Pink
24
Lists

Cell Formulas
Range	Formula
B2:B10	B2	=TEXTJOIN(", ",,VLOOKUP(TRIM(TEXTSPLIT(A2,",")),$A$16:$B$23,2,0))

It works like a gem!, thank you thank you!

I can't tell you how much you've helped me today and saved me from a very painful task

 

[Fluff]

You're welcome & thanks for the feedback.