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 <tbody> </tbody>
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

<tbody>
</tbody>

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

<tbody>
</tbody>

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]

bump