Hi Edwin

Perhaps you could modify the following array formula to suit your needs
{=CHOOSE(MAX((ISNUMBER(SEARCH
({"Red","Green","Yellow"},A21))*{1,2,3}))+1
,"No","Red","Green","Yellow")& " Product"}

Use Ctrl+Shift+Enter (CSE) to commit on Edit the formula rather than
just Enter.
Do not type the curly braces { } yourself, Excel will enter them
when you use CSE

You can extend the list "Red","Green" in both sets of data, but you
need to add to the 1,2,3 for each addition you make to the range. The
"No" at the beginning of the second range, is to deal with the case
where none of the colours are found.

--
Regards

Roger Govier


"Edwin Mashiringwani" wrote in message
...
Thank you RD for your quick response. The example formula you
suggested does work.
As it happens I have other text where your suggested formula would
look like this
=IF(ISNUMBER(SEARCH("Red",A1)),"Red Products","")

Is there another formula I can use that recognise that if "Red" is
found anywhere in Column A , the result in Column B would be " Red
Products" , as well as handling "Gre" in Col A to have a result of
"Green Products" and other variations ?

Thank you in advance
Edwin


"RagDyeR" wrote in message
...
This works for the *one* example you gave.

=IF(ISNUMBER(SEARCH("Gre",A1)),"Green Products","")

Leaving Column B blank if it doesn't find "Gre",
BUT ... don't you have *other* text to look for?

A different formula would be called for if you have a large datalist
to look
for.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Edwin Mashiringwani" wrote in message
...
Hi All,

I have a situation where I need to fill Column B with Text Depending
on what
Text is in Column A eg

Column A Column B

1 GREAPPLES GREEN Products
2 PEARSGREEN GREEN Products
3 GreenGarbage GREEN Products

In other words I would be using GRE as the condition in Col A for a
product
to classed as GREEN Products
I would be grateful if someone can point me in the right direction.
Thank you

Edwin