Categorize items based on free text

[ALMOHANNADI]

Hi,
Can we categorize items based on free text using Excel?
I have attached the picture which shows example of what I need to build.
The formula should:

1. Read the free text, find key words in the category table then categorize the item.
2. In case of having more that one match, it should pick the category that have more matches.
3. In case of having same number of match, it should pick up the category based on the MAX. historical records in the categorized items.
4. The list of key words will be maintained in separate sheet. This is just an example.

Appreciate your help.

 

[ALMOHANNADI]

hope someone can help