VLOOKUP/OFFSET Problem
 

Hi all
Have a huge worksheet and loads of dates and data. It is regarding
stockmarkets and trading for my thesis. I am looking up dates and
returning a value using a VLOOKUP formula
=VLOOKUP(S2;LAIS2!$A$3:$G$438;7;FALSE) and it is working like it
should.
I am trying to find out trading dates before and after the value the
VLOOKUP returns. I thought of using a OFFSET formula but that does not
seem to work with the VLOOKUP.

Does anyone understand what I am trying to do and able to help me?
Regards,
Vilhelm


--
XavierXXX
------------------------------------------------------------------------
XavierXXX's Profile: http://www.excelforum.com/member.php...o&userid=28875
View this thread: http://www.excelforum.com/showthread...hreadid=486221

VLOOKUP/OFFSET Problem
 

Vilhelm,
in your formula, is S2 a date? Or is the result of the formula a date
(in which case dates are in column G:G)?
OFFSET() will find reference a cell as an offset from its position, not
its value. VLOOKUP() finds a value, not a position. MATCH() will find a
relative position within an array. Thus,
MATCH(VLOOKUP(...)-1, ...) would find the location of the previous
value in the array.
INDEX() will find the nth element in an array. The combination
INDEX(..., MATCH(...)) is often used as a substitute of VLOOKUP when
there is more complexity in the required calculations than VLOOKUP()
can handle.

Write back if you need something more specific or get stuck.

HTH
Kostis Vezerides