Searching for text within cells

[Alan]

I want to create an IF statement that does something like this:

(Formula in cell B1) - IF A1 contains "red" then return "red", else if A1
contains "blue", return "blue", else if A1 contains "black" return "black"
else .... and so on. I realise I have a 7 nested if limit.

Can anyone help?

TIA, Alan

 

[Don Guillett]

Well. The way you stated it
=a1
would do it.

 

[papou]

Hello Alan
There may be simpler but anyway here's a try:
=INDEX({"blue";"red";"black"},MATCH(A1,{"blue";"red";"black"},0))
And you may add some test with somthing like :
=IF(NOT(ISERROR(....

HTH
Cordially
Pascal

 

[Soo Cheon Jheong]

Alan,

=INDEX({"Blue";"Red";"Black"},SUMPRODUCT((
COUNTIF(A1,"*"&{"Blue";"Red";"Black"}&"*")>0)
*ROW($1:$3)))


--
Regards,
Soo Cheon Jheong
Seoul, Korea
_ _
^¢¯^
--