#1   Report Post  
steve alcock
 
Posts: n/a
Default vlookup ranges

hi,

my next question is :

I want to vlookup a value in a range say :

j1:d500

i.e. 0020091 is the lookup value

but on the worksheet the lookup value is in j3 and the
return value is in d20 how do I get this array to work I
have tried alsorts but cannot succeed.

anyone help here please

thanks

steve


  #2   Report Post  
JulieD
 
Posts: n/a
Default

Hi Steve

vlookup is designed to look up a value in the leftmost column of the table
and return a value from the same row from another column in the table,
if the value you're looking up is in row 3 of your lookup_table and the
value you want to return is in row 20 then you can't use the VLOOKUP
function to do it.

To determine a possible solution we need to know the relationship between
the lookup_value in the table (column J) and the return value (column D) are
they always 17 rows apart? if not, how do you know where what you're looking
for in column J is located in column D?

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"steve alcock" wrote in message
...
hi,

my next question is :

I want to vlookup a value in a range say :

j1:d500

i.e. 0020091 is the lookup value

but on the worksheet the lookup value is in j3 and the
return value is in d20 how do I get this array to work I
have tried alsorts but cannot succeed.

anyone help here please

thanks

steve




  #3   Report Post  
steve alcock
 
Posts: n/a
Default

Hi Julie,

on banking I get a cheque value number from another cell
automatically, this is the start of my vlookup;
in the next column I want to vlookup this value on another
sheet, same book, which is located in JX and the value I
want is in dX

the cheque number and the value are on the same line and
always JX and DX

any further help would be appreciated

regards

steve



-----Original Message-----
Hi Steve

vlookup is designed to look up a value in the leftmost

column of the table
and return a value from the same row from another column

in the table,
if the value you're looking up is in row 3 of your

lookup_table and the
value you want to return is in row 20 then you can't use

the VLOOKUP
function to do it.

To determine a possible solution we need to know the

relationship between
the lookup_value in the table (column J) and the return

value (column D) are
they always 17 rows apart? if not, how do you know where

what you're looking
for in column J is located in column D?

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"steve alcock"

wrote in message
...
hi,

my next question is :

I want to vlookup a value in a range say :

j1:d500

i.e. 0020091 is the lookup value

but on the worksheet the lookup value is in j3 and the
return value is in d20 how do I get this array to work I
have tried alsorts but cannot succeed.

anyone help here please

thanks

steve




.

  #4   Report Post  
JulieD
 
Posts: n/a
Default

Hi Steve

one option is to use the OFFSET and MATCH functions

=OFFSET(Sheet2!$J$1,MATCH(A1,Sheet2!$J$1:$J$20,0)-1,-6)


--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"steve alcock" wrote in message
...
Hi Julie,

on banking I get a cheque value number from another cell
automatically, this is the start of my vlookup;
in the next column I want to vlookup this value on another
sheet, same book, which is located in JX and the value I
want is in dX

the cheque number and the value are on the same line and
always JX and DX

any further help would be appreciated

regards

steve



-----Original Message-----
Hi Steve

vlookup is designed to look up a value in the leftmost

column of the table
and return a value from the same row from another column

in the table,
if the value you're looking up is in row 3 of your

lookup_table and the
value you want to return is in row 20 then you can't use

the VLOOKUP
function to do it.

To determine a possible solution we need to know the

relationship between
the lookup_value in the table (column J) and the return

value (column D) are
they always 17 rows apart? if not, how do you know where

what you're looking
for in column J is located in column D?

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"steve alcock"

wrote in message
...
hi,

my next question is :

I want to vlookup a value in a range say :

j1:d500

i.e. 0020091 is the lookup value

but on the worksheet the lookup value is in j3 and the
return value is in d20 how do I get this array to work I
have tried alsorts but cannot succeed.

anyone help here please

thanks

steve




.



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
VLOOKUP Function using Data Ranges. Cal Excel Worksheet Functions 6 April 16th 05 03:26 PM
How do I do a VLOOKUP from 2 ranges and add the results together? Wendy Excel Worksheet Functions 4 April 11th 05 10:38 PM
Vlookup with 2 ranges in one worksheet Eelco Wiertsema Excel Worksheet Functions 5 February 18th 05 08:57 PM
vlookup and named ranges chathag Excel Worksheet Functions 3 November 22nd 04 05:07 PM
vlookup and named ranges Domenic Excel Worksheet Functions 0 November 16th 04 05:08 PM


All times are GMT +1. The time now is 07:31 PM.

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"