IF Statement with multiple possible results

[D Hills]

I'm trying to write a formula the returns a specific answer based on a number in another cell:
Cell value is <0, return "RED"
Cell value is >0, but <.099, return "YELLOW"
Cell value is >=.10, but <.199, return "GREEN"
Cell value is >.20, return "BLACK"

I have done this for the actual cell with conditional formatting, but I want my result in a different cell than the factor number is actual in

 

[AlanSidman]

Assume your data in A1, then

=IF(A1<0, "RED",IF(AND(A1>0,A1<0.099),"YELLOW",IF(AND(A1>=0.1,A1<0.199),"GREEN","BLACK")))

 

[bosco_yip]

Or using LOOKUP function.

The condition is :
Cell value is <0, return "RED"
Cell value is >=0, but <=0.099, return "YELLOW"
Cell value is >=0.10, but <=0.199, return "GREEN"
Cell value is >=0.20, return "BLACK"

[B2] =LOOKUP(A2,{-9.9E+307;0;0.1;0.2},{"RED";"YELLOW";"GREEN";"BLACK"})

 

[Peter Bartholomew]

I would go with the LOOKUP formula (it is in column 7 below).
The thing that is not so clear is what purpose does the message serve? To communicate with the user, it can be argued that the a conditional format (as in column 8) does it better. If the purpose is to adjust dependent formulas then an integer code (columns 8 or 9) is more effective than testing for colour by name. Such a code can be used as an INDEX or an option in CHOOSE, e.g.
= CHOOSE( 1+State, Choice1, Choice2, Choice3, Choice4 )

 

[D Hills]

Assume your data in A1, then

=IF(A1<0, "RED",IF(AND(A1>0,A1<0.099),"YELLOW",IF(AND(A1>=0.1,A1<0.199),"GREEN","BLACK")))

Thank you Alan. Your formula worked perfectly. I then added the conditional formatting to the result to change the cell the specific color. The purpose for this was to highlight profitable products to be sold by our sales team so they could sell a more profitable mix.
Thanks again!