Selecting a Keyword

[Gmata]

Hello guy i am trying to come up with a formula but i have no idea how i need
some help from the experts.

I have two columns,
1 contains information and the other one is empty where the formula is going
to be called "product type"

So what i need is:

If column A contains the word: Apple, strawberry, banana. Column B should
be = Fruits

If column A contains: tomatoe, carrot, onion B = Vegetable

If columb A contains anything other than: Apple, strawberry, banana,tomatoe,
carrot, onion. B= Beef

Is it possible to create this formula?

Thanks

 

[T. Valko]

One way...

Create a 2 column lookup table like this:

apple...fruit
strawberry...fruit
banana...fruit
tomatoe...vegetable
carrot...vegetable
onion...vegetable

Assume that table is in the range D1:E6

Then use this formula in column B:

=IF(COUNTIF(D$1$6,A1),VLOOKUP(A1,D$1:E$6,2,0),IF(A1<>"","Beef",""))

 

[Gmata]

THe formula works great, but how can i modified it so in case it contains the
keyword strawberry it gets strayberry even if there is another keyword in the
cell.

For example:

Strawberry Cake = Strawberry
Apple Pie = Apple
chocolate Strawberry = Strawberry

 

[T. Valko]

Ok, it's more complicated...

Still using the lookup table in D1:E6.

Array entered** :

=IF(COUNT(SEARCH(D$1$6,A1)),LOOKUP(1E100,SEARCH(D$1$6,A1),E$1:E$6),IF(A1<>"","Beef",""))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Note that if there are multiple keywords in a cell like:

Strawberry Onion

The formula will match whichever keyword appears in the lookup table
furthest down the list. So, for an entry like Strawberry Onion, the formula
will match both keywords but will return vegetable because onion appears in
the lookup table below Strawberry.