Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Andre Croteau
 
Posts: n/a
Default 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é





  #2   Report Post  
Jim May
 
Posts: n/a
Default

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é







  #3   Report Post  
Dave Peterson
 
Posts: n/a
Default

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
  #4   Report Post  
Andre Croteau
 
Posts: n/a
Default

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é







Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Setting source data range with Charts D Charts and Charting in Excel 2 January 1st 06 03:51 AM
Help with data not getting plotted Scott Ehrlich Charts and Charting in Excel 1 January 23rd 05 06:15 PM
Charting data ranges that change mikelee101 Charts and Charting in Excel 2 December 17th 04 12:07 AM
Word field codes in Excel data file Includetext mranz Excel Discussion (Misc queries) 1 December 8th 04 12:19 AM
S.O.S :importing excell data into word villi Excel Discussion (Misc queries) 0 November 25th 04 09:37 PM


All times are GMT +1. The time now is 11:39 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"