Check cell for word, If word exists in cell, assign that cell to corresponding category.

Hi.
I've tried a bunch of ways to do this and nothings been working. The Lookup and Match functions are not doing what I want. Is there a way for VBA to handle this problem?

Problem.
Sheet1(
Column A contains names. These names are strings which contain Key words.
Column F is blank. I want column F to be filled in with categories that those keywords belong to.
)
Sheet2(
This contains a table. The Horizontal Axis is a list of Categories. The Vertical access is just a list of words
associated with each category.

I've attached a simplified example.

If anyone can help with this It would be greatly appreciated.