Vlookup partial text in cell

Hello,
I have attached sample spreadsheet got from online source.
But i'm facing problem for highlighted in red cell pulled values are wrong. If text doesn't match den it should be blank.

Data Tab A consist of Raw Data and B contains formula to pull partial text data from Keywords tab.

Thanks in advance for views and your help.

Hi All,

an attempt. You have to move LS at the bottom of your list, in order to obtain better results

Please Login or Register  to view this content.

Just enter to confirm formula in B2.

Hope it helps

Hi,

thanks for feedback

Please Login or Register  to view this content.

You have to play with you traduction list in order to fine tune (as far is possible) outputs from formula.

Regards

Hi,

unfortunately I'm at zero level with Vba. I hope you receive better solutions from some friends of the Forum.

Regards

Hi again

this formula does not work as a vlookup, so be careful expecially if you're using -among keywords- short strings (LS for example).


Hope it helps

here's 1 solution that can solve most of the problems i guess (my formula will not consider the file extensions. all the xlsx, zip, etc):

Formula:

Please Login or Register  to view this content.

row 25 is different from your desired answers because "Raymond James" has 2 brokers in keywords. Ray James & Raymond James. my formula will pick the last one.

there is also an issue which didn't surface coincidentally. you have a broker name "GS". in row 28, you have:
ALXN Earnings Model - Post-4Q13 Request_Stifel.xlsx
my formula will actually give you "GS" if "Stifel" is placed above "GS" under the Keywords sheet. and that would be wrong i guess

and that is the problem. i can't help you VBA, but even if you do want it, there has to be some consistency. i suppose when it looks for "GS", it has to be a word by itself right? and not something that contains "GS". but what happens then for cases like row 15?
CPHD Client Model Q4'13_CraigHallum.xlsx
you actually want the formula to look for something containing "Craig". so there is no consistency at all. i doubt VBA can help. my above formula works if you are looking for something containing the keyword. if you want the exact word by itself:
=IFERROR(LOOKUP(2^15,SEARCH(" "&Keywords!$A$2:$A$2103&" "," "&SUBSTITUTE(SUBSTITUTE(A2,"_"," "),"."," ")&" "),Keywords!$B$2:$B$2103),"")