Vlookup of parts of a word in a range of Data
 

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 D1:D100), 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é

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

"Andre Croteau" wrote in message
...
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 D1:D100), 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é

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 wrote:

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 D1:D100), 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é

--

Dave Peterson

Hello Jim, Dave,

Both formulae work very well!

Thanks!

André


"Andre Croteau" wrote in message
...
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 D1:D100), 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é