Forum Discussion

Copper Contributor

Oct 26, 2020

Solved

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

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 ...

BI & Data Analysis

excel

Formulas and Functions

office 365

Detlef_Lewin
Oct 26, 2020
erikhalverson
=LOOKUP(2,1/SEARCH($C$2:$C$87,A2),$D$2:$D$87)

NikolinoDE

Platinum Contributor

Oct 26, 2020

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)

erikhalverson
Copper Contributor
Oct 26, 2020
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.
Sample Excel.xlsx59 KB
- Detlef_Lewin
  Silver Contributor
  Oct 26, 2020
  erikhalverson
  =LOOKUP(2,1/SEARCH($C$2:$C$87,A2),$D$2:$D$87)
  - erikhalverson
    Copper Contributor
    Oct 26, 2020
    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?