How to use VLOOKUP to return a textstring based on a value as a part of a textstring in another cell

[erikhalverson]

Hey, sorry if the title is unclear, but what I'm looking for actually seems pretty simple (to me, at least).

I've attached a screenshot of my workbook below. Essentially, I have the title of the product in F4, and I need to automatically populate the keyword textstring (currently it's #N/A because the formula I'm using is incorrect). I've create a table in LA:LB with the textstring that's supposed to be returned in LB based on the partial match value in LA.

To make a little more sense, In I4, I want to return automotive-headlight-assemblies based on the word headlight in F4. I've looked all over the internet, but can't seem to find a VLOOKUP to handle this.

[#2]

@erikhalverson 

With your permission, if I can recommend. It can help us all if you upload an Excel file (without sensitive data), no picture. Even if it is said that a picture can say a thousand words, it is certainly not in the case of Excel, on the contrary in some cases. This would also be a blessing for all of us, as we can understand the problem much better, a win-win situation for everyone.

Thank you for your understanding and patience

Nikolino

I know I don't know anything (Socrates)

[#3]

@NikolinoDE Hey, I've attached a sample of the workbook below. I just want B2 to take the keywords in Column C, look for them in A2, and then populate B2 based on the matching value in Column D. (So if A2 says "headlight" I want B2 to autofill with "automotive-headlight-assemblies", because that's what I have matching up in the table C:D.

[#4 · Accepted solution]

@erikhalverson 

=LOOKUP(2,1/SEARCH($C$2:$C$87,A2),$D$2:$D$87)

[#5]

@Detlef Lewin That worked!!!! Thank you so much!!!

Additional question - how the heck did you even know that? Did you just know the right formula off-hand, or do you have a good way of looking it up?

[#6]

@erikhalverson 

It's a standard solution. You will find it all over the place.