Formula/macro to search cell text and assign value?

[shibbyshibby]

Sorry if this question has been asked before, I couldn't find the answer after hours of googling:

I am trying to get a code which searches each row for a keyword, and then assigns a value to it depending on what group of keywords it is.

For example, if the Name or Comment mentions certain fruit keywords, I want it to assign the value: "Fruit", and if it mentions certain vegetable keywords, I want to assign it "Vegetables", and if it is unrelated to either, I want to keep it blank. If the name/comment mentions both fruit and vegetables then I want it to say "Error".

Since the keywords I am using are mostly two letters, I want the search to only search for the two letters on its own, and not as part of a sentence. e.g. if the key word is ED, I don't want it to return a value for "I rested". Instead, I want it to stay blank.

i.e. I want the code to take information from column A and B, and assign a value based on the keywords in column D and E. As an example:
A B C
Name Comment Value
V1 Eating vegetables are good for you Vegetable
null lock out of ep. Fruit
S-1 It is JUST
Fruit-sp Error

D E
Fruit Vegetable
EP SW
KG SP
ST CM
BV LP

The formula I used so far is this: where C3 is the first value
C3=IF(OR(ISNUMBER(LOOKUP(100,SEARCH(Sheet1!$D$2:$D$6,A3))),ISNUMBER(LOOKUP(100,SEARCH(Sheet1!$D$2:$D$6,B3)))),"Fruit","")&IF(OR(ISNUMBER(LOOKUP(100,SEARCH(Sheet1!$E$2:$E$6,A3))),ISNUMBER(LOOKUP(100,SEARCH($E$2:$E$6,B3)))),"Vegetable","")

In columns D and E I put a leading and trailing space between each word to only search for the keywords themselves, and not as part of a word. (e.g. ST is correct instead of haSTe).

This formula seems to work only for 60% of the information, problems occur when the keyword is at the end of comment sentence: the value is blank. e.g. in row B4 of the table I made above my formula returns a blank.

I can't figure out how to fix this. Maybe a macro would be more suited to solve this problem? However I am not too savvy in VBA.

Thanks

[shibbyshibby]

Bringing
Up
My
Post