Search for keywords in a cell and return the adjacent keyword category

[gavcol]

Hi,

I have a similar request to another very old thread which I was going to reply to but the BB advised to create a new post
Search text in a string and return adjacent cell of first value found

My variation to that thread is where I have two tables, a data table and a keyword table.
In the data table I need a formula in the 2nd column (C3) to look in the adjacent cell in the first column (A3) for one of the keywords from the keyword table (Tbl_KeywordCats[Keyword].
Once found, then return the respective category from Col G for that keyword.
I've been able to do a lookup to search for the keyword value but not return the adjacent category. It only returns True/False.

Using similar example from the above thread..........

Find-Keywords-4Cats.xlsb
	B	C	D	E	F	G
1	Tbl_Data				Tbl_KeywordCats
2	Data	Category			Keyword	Category
3	En John Smith 324334	Cat1			Paul Jones	Cat1
4	Fr Paul Jones 324246				John Smith	Cat1
5	John Smith TE 8564645				Joe Bloggs	Cat2
6	GT John Smith 845754				Jane Doe	Cat3
7	HG Sandra Frey 671182				John Doe	Cat3
8	KJ Paul Jones 85662
9
Sheet1

Kinda pulling my hair out with this one and would appreciate any help.
Thanks in advance.
Gav

I have Excel® for Microsoft 365 MSO (Version 2304 Build 16.0.16327.20200) 64-bit

 

[Skybluekid]

Try the below:

Book1.xlsm
	A	B	C	D	E	F	G	H
1
2			Category			Keyword	Category
3						Paul Jones	Cat1
4		En John Smith 324334	Cat1			John Smith	Cat1
5		Fr Paul Jones 324246	Cat1			Joe Bloggs	Cat2
6		John Smith TE 8564645	Cat1			Jane Doe	Cat3
7		GT John Smith 845754	Cat1			John Doe	Cat3
8		HG Sandra Frey 671182	#N/A
9		KJ Paul Jones 85662	Cat1
10
11
12
Sheet2

Cell Formulas
Range	Formula
C4:C9	C4	=INDEX($G$3:$G$7,MATCH(1,--ISNUMBER(SEARCH($F$3:$F$7,B4)),0))

 

[gavcol]

@Skybluekid - Awesome, Thank you !!
Once I converted the static ranges to the table names (and then applied it to the real data set), it worked perfectly
(even after I noticed my sample data hadn't been updated properly to include an example of each category, so here's the finished result (pre real data)

	Tbl_Data				Tbl_KeywordCats
	Data	Category			Keyword	Category
	En John Smith 324334	Cat1			Paul Jones	Cat1
	Fr Paul Jones 324246	Cat1			John Smith	Cat1
	John Smith TE 8564645	Cat1			Joe Bloggs	Cat2
	GT John Smith 845754	Cat1			Jane Doe	Cat3
	HG Joe Bloggs 671182	Cat2			John Doe	Cat3
	KJ John Doe 85662	Cat3

Cat1

I'm new to the xl2bb addin and haven't figured out the cell formulas bit yet so here's the formula I used
=INDEX(Tbl_TransMapKeyword[Category],MATCH(1,--ISNUMBER(SEARCH(Tbl_TransMapKeyword[Keyword],[@Data])),0))

 

[Skybluekid]

You are very welcome