partial vlookup

[ishaan.shekhar]

Hello Community,

I need to partially match value from current sheet to a remote sheet using vlookup. e.g. below, I need to match "GOOD MORNING WORLD" with "MORNING" in 2nd sheet and get the second column in 2nd sheet after match.

Column 1, Column 2
GOOD MORNING WORLD, <get value from LOOKUP TABLE SHEET after partial match... i.e. 123>


LOOKUP TABLE SHEET:

Column A,Column B
MORNING, 123
NIGHT, 256


Is there a way to do this please? Please let me know if I was not clear with my problem and I will try explaining it differently. Thanks!

[tim201110]

=VLOOKUP("*"&C3&"*",TABLE SHEET,2,)
in C3 MORNING

[JohnTopley]

Try

=INDEX(Sheet2!$B:$B,SUMPRODUCT(--(ISNUMBER(SEARCH(Sheet2!$A$2:$A$3,A2)))*(ROW(Sheet2!$A$2:$A$3))))

Sheet2!A2:B3 is your "lookup sheet"