Searching for text within cells

A

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
 
P

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
 
S

Soo Cheon Jheong

Alan,

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


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