Formula to match any part of string in cell to lookup table

[bentham7246]

Hi, I've been scratching my head all day on this one. I feel like it should be easy but I can't fathom out how to do it. Any help would be much appreciated

So here's a sample:

Item Colour	Colour Lookup
OFF WHITE	WHITE
DARK BLUE	BLUE
GREY MARL	GREY
NAVY/RED/GREY	MULTI

<tbody>
</tbody>

Lookup Table

Colour List

BLUE

RED

GREY

WHITE

BLACK

<tbody>
</tbody>

So basically I have a big list of items each with a unique colour but they will be different variations such as light blue, dark red, midnight blue, honey orange etc etc. I'm just looking to match one of the words from my item list to my colour look up table so I can try to categorize items into colour groups


Can this be done through formula or is it a VBA task?

Thank you

 

[steve the fish]

Place first table in A1:B5. Second in E1:E6. In B2:

=IF(SUM(INDEX(--(ISNUMBER(SEARCH($E$2:$E$6,A2))),0))>1,"MULTI",INDEX($E$2:$E$6,MATCH(1,INDEX(--(ISNUMBER(SEARCH($E$2:$E$6,A2))),0),0)))

 

[bentham7246]

That worked perfectly!!!! Thanks Steve the fish