Vlookup of parts of a word in a range of Data

[Andre Croteau]

Hello,

Is it possible to do a lookup of a portion of a word within a certain range
of cells, then give the value of the cell next to the cells found?

In column A Cells A1:A5, I have a series of five 3 letter names

abc
def
ghi
jkl
mno


In column D (cells D1100), I have a long range of data (words) that might
include the 3 letter words, and in column E its corresponding value

Column D Column E

sfert def fgat 56
jklb bbgd ssh 65
adh ghi sf 87
hhsyukl sgh 71
hhsytr kksy 43
mnogh ffsr 12
etc.... etc....


I would like to show in column B (Cells B1:B5) a formula that would give me
the value in column E coresponding to where the 3 letter word was found in
column D (can only be max of one possibility)

Col A Col B

abc #N/A
def 56
ghi 87
jkl 65
mno 12


I have tried the following array formula in cell B1, but the results gives
me #Value

{=SUM((FIND(A1,$D$1:$D$100)>0)*($E$1:$E$100))}

Can you please help?

Thanks in advance.

André

 

[Jim May]

In cell B1 enter:

=IF(ISNUMBER(MATCH("*" & A1 & "*",$D$1:$D$100,0)),INDIRECT("E"&(MATCH("*" &
A1 & "*",$D$1:$D$100,0))),0)

and copy down to B5.
HTH

 

[Dave Peterson]

Maybe this will work.

=VLOOKUP("*"&A1&"*",$d$1:$e$100,2,FALSE)

But abc will match both:
defgabcdef higk
and
defasd abc qwera

So take a look at the output before you trust it.

 

[Andre Croteau]

Hello Jim, Dave,

Both formulae work very well!

Thanks!

André