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
.