Categorize items based on free text

[amohannadi]

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:
Read the free text, find key words in the category table then categorize the item.
In case of having more that one match, it should pick the category that have more matches.
In case of having same number of match, it should pick up the category based on the MAX. historical records in the categorized items.
The list of key words will be maintained in separate sheet. This is just an example.

Appreciate your help.

[JohnTopley]

Please read the yellow banner at the top of this page on how to attach a file.

[amohannadi]

Please read the yellow banner at the top of this page on how to attach a file.

Thank you, I have attached the file

[JeteMc]

Not elegant, however the following returns the desired results.
F3:W4 are populated by copy/pasting transpose the table from the Cat. sheet.
Formula for F5:W9 =IFERROR(SEARCH(F$4,$C5),"")
X3:AA3 are populated with the four categories.
Formula for X5:AA9 =SUMPRODUCT(($F5:$W5<>"")*($F$3:$W$3=X$3))
Formula for D5:D9 =IF(AND(MAX(X5:AA5)=1,X5=1),X$3,INDEX(X$3:AA$3,AGGREGATE(14,6,(COLUMN(X5:AA5)-COLUMN(W5))/(X5:AA5=MAX(X5:AA5)),1)))
Note that F5:AA9 may be moved and/or hidden for aesthetic purposes.
Let us know if you have any questions.