Search Text String for Match in a Column of Key Words

[letsgobuffalo]

Hello, long-time reader first time poster.

My data:
Table1 - Column with detailed transaction descriptions (Description)
Table2 - Column with a list of key words (VendorKey) and column with vendor names (Vendor)

My problem:
Add a column to Table1 that returns a value from the Vendor column in Table2 by finding the VendorKey in Table2 within the the Description in Table1

My in-process solution:
Using Sumproduct, I'm able to get a value of 1 a value in the VendorKey column is found within the Description column and a zero if it's not. But I am unable to figure out how to find the row that the matching VendorKey is found in order to pull in the Vendor. Below is the formula I have so far. Any help is very much appreciated!

=SUMPRODUCT(--(NOT(ISERR(SEARCH(Table2[[#All],[VendorKey]],[@Description])))))

[FDibbins]

Hi, welcome to the forum

(sounds like homework, but you have made a good start)

Have you looked at using INDEX/MATCH or VLOOKUP for this?

upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

[letsgobuffalo]

Thank you sir for your quick reply! Attached is the sample file.

I tried to incorporate Index/Match/Row but I wasn't able to get it to work. I did write a query in Access that worked but I'd rather keep this in Excel if I can.

Thanks again!

[FDibbins]

Sorry for the delay in responding

So will the 1st word always be "the" followed by the 1-or-2 word phrase you are looking for?

[JohnTopley]

Try

in B3

=IFERROR(INDEX($E$3:$E$5,IF(SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$5,$A3))*ROW($F$3:$F$5)),SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$5,$A3))*ROW($F$3:$F$5))-2,"")),"")

copy down

OR

=IFERROR(INDEX(Table2[Vendor],SUMPRODUCT(--ISNUMBER(SEARCH(Table2[VendorKey],[@Description]))*ROW(Table2[VendorKey]))-2),"")


(Not a great fan of Tables!!)

[Phuocam]

Or:

=IFERROR(LOOKUP(2,1/SEARCH(Table2[VendorKey],[@Description]),Table2[Vendor]),"")

[letsgobuffalo]

JohnTopley & Phuocam - Both solutions work! Thank you both very much!!

Having the ability to do this look-up will be a big productivity improvement to our expense coding process. I am very grateful for the assistance.