Assigning categories to different keywords from a text

[LOGi]

Hi!

I'm trying to assign different categories to keywords from a text. Texts I want categorized can be e.g. "lunch boston james" or "field expenses anderson". From here I want to find the different names in the sentence (both first and last names), and each name has different categories or divided into different groups.

E.g. "lunch boston james " category = marketing, where I have another sheet with E.g.
Names category
James hamilton marketing

I have tried this formula:

=OFFSET('Key-Sheet'!$B$1,MATCH(MIN(IFERROR(SEARCH('Key-Sheet'!$A$1:$A$6,$A1),LEN($A1)+1)),SEARCH('Key-Sheet'!$A$1:$A$6,$A1))-1,0)

However, it only seem to work with one name and not both names. I want the formula to look for both names in each text because sometimes first names are written and sometimes the last names.

Can anyone help me with this?

Thanks in advance.

 

[r1c1]

@LOGi Welcome to Chandoo.org forums and thanks for posting your question.

Very interesting indeed. For better data management purposes, I recommend storing both names in 2 columns. But assuming the name is one cell and always in first name space last name format below solution should work.

=INDEX(cats,MATCH(1, 
IFERROR(SEARCH(LEFT(names,FIND(" ",names)-1),A3)>0,FALSE) + IFERROR(SEARCH(MID(names,FIND(" ",names),99),A3)>0,FALSE),0))

the named range cats refers to categories and names refers to the names.