Multiple SEARCHes within same TEXT string

[Me!]

Hi,

I have a column which contains text strings that represent colours.
However, the *actual* colour is often buried in other text i.e.
METBLACKPAINT.

I have a list of 12 single-word colours i.e. BLACK, and want to identify in
one formula which (if any) of the 12 is buried in the text string (and then
have the formula return that colour)

If I had just 7 colours, I'd just go with nested IFs and SEARCH, but as I
have 12 that is not an option.

Any ideas would be greatly appreciated.

Many thanks,

Jason

 

[Max]

Assume your 12 single-word colours eg: BLACK, WHITE, etc are in a defined
col range: Colours
In A2 down are the strings, eg: METBLACKPAINT
Try in B2, normal ENTER:
=INDEX(Colours,MATCH(TRUE,INDEX((ISNUMBER(SEARCH(Colours,A2))),),0))
Copy down
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500, Files:362, Subscribers:62
xdemechanik