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:

[TABLE="class: outer_border, width: 500, align: left"]
<tbody>[TR]
[TD]Item Colour[/TD]
[TD]Colour Lookup[/TD]
[/TR]
[TR]
[TD]OFF WHITE[/TD]
[TD]WHITE[/TD]
[/TR]
[TR]
[TD]DARK BLUE[/TD]
[TD]BLUE[/TD]
[/TR]
[TR]
[TD]GREY MARL[/TD]
[TD]GREY[/TD]
[/TR]
[TR]
[TD]NAVY/RED/GREY[/TD]
[TD]MULTI[/TD]
[/TR]
</tbody>[/TABLE]









Lookup Table

[TABLE="class: outer_border, width: 500, align: left"]
<tbody>[TR]
[TD]Colour List[/TD]
[/TR]
[TR]
[TD]BLUE[/TD]
[/TR]
[TR]
[TD]RED[/TD]
[/TR]
[TR]
[TD]GREY[/TD]
[/TR]
[TR]
[TD]WHITE[/TD]
[/TR]
[TR]
[TD]BLACK[/TD]
[/TR]
</tbody>[/TABLE]











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