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

[Trevasaurus]

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.

[jindon]

UDF

=Getregion(A2,Sheet2!$A$1:$E$6)

Option Explicit
Option Compare Text

Function GetRegion(txt As String, rng As Range) As String
    Dim a, i As Long, ii As Long
    a = rng.Value
    For ii = 1 To UBound(a, 2)
        For i = 2 To UBound(a, 1)
            If txt Like "*" & a(i, ii) & "*" Then
                GetRegion = a(1, ii)
                Exit For
            End If
        Next
        If Len(GetRegion) Then Exit For
    Next
End Function