Longer Text Lookup within Shorter Text String

[acool]

Hi Everyone,

I am currently trying to create an excel formula which uses a lookup formula to see if the longer text strings in Column D match the shorter values in Column B. If there is a text match, I would like the corresponding shorter text value to be provided in column E. For example, for Cell E1 I would like the text value "Dog" to be returned, for E2 "Horse", etc. Any help would be greatly appreciated. Thank You!

 

[Sergius]

It turned out a little scary, but it seems to be what you need.

Book1.xlsm
	D	E	F
17	dog	big dog	dog
18	pig	red pig	pig
Sheet3

Cell Formulas
Range	Formula
F17:F18	F17	=IF(SUBSTITUTE(RIGHT(MID(" "&SUBSTITUTE(E17," ",REPT(" ",999)),1,999*2),999)," ","")=D17,SUBSTITUTE(RIGHT(MID(" "&SUBSTITUTE(E17," ",REPT(" ",999)),1,999*2),999)," ",""),"")

 

[Scott Huish]

=FILTER($B$1:$B$8,ISNUMBER(SEARCH($B$1:$B$8,D1)))

Copy down.