find a value in a text string and return the value

[monsierexcel]

if i have a string "bob has white trousers"
i want the formula to search multiple colours and return the value white.

so my formula would contain something like SEARCH( "black", "white", "grey", "green", "blue") etc
If it had multiple values i would want to just accept the first.

I cant get my head around doing this apart from making multiple IF statements and i am sure there is an easier way!
THANK YOU GUYS

 

[steve the fish]

Try:

=INDEX({"black","white","grey","green","blue"},MATCH(TRUE,ISNUMBER(SEARCH({"black","white","grey","green","blue"},A1)),0))

 

[monsierexcel]

thank you ever so much, thats spot on!!!