Find Color in the string using formula.

[LalitPandey87]

Hi all,

I have some string in a column e.g:-

The pen is blue.
Red eyes of devil.
Sky is blue.
White is my favorite color.


and in other column i have some color listed e.g:-

Red
Green
Blue
Black
Brown
White
Pink

now i want the color in the string based on the color listed in other column e.g:-

The pen is blue. blue
Red eyes of devil. Red
Sky is blue. Blue
White is my favorite color. White


only by using formula.

thanks in advance

[NBVC]

Assuming sentences are starting in A2 and your colour table is in F2:F8, then try:

=LOOKUP(9.9999E+307,SEARCH($F$2:$F$8,A2),$F$2:$F$8 )

copied down

[LalitPandey87]

Thanks NBVC...

[Norm]

Can you explain what the 9.9999E+307 represents?

[Admin]

Hi

Have a look http://www.excelfox.com/forum/f23/number-9-99999999e-307-excel-formula-437/