Data Entry Formula based on IF or something Better

[Edwin Mashiringwani]

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

 

[RagDyeR]

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!
=====================================================


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

 

[Edwin Mashiringwani]

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

 

[Roger Govier]

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.