I have a spreadsheet with column A being list of dates (from 1/1/08 - 31/12/10)
in order and column B a list of codes. Each codes periodically is repeasted
in no fixed order. Column C and D have data that relate to the code in column
B.

Please note that these formulas are array formulas. You create array formulas
in the same way that you create other formulas, except you press
CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can
notice the curly braces at both ends like "{=<formula}"

In cell E1 enter the code

'To return the last values based on date from ColC and ColD copy this
formula and copy across to the right

=INDEX(OFFSET(C$1,0,0,MATCH(TODAY(),$A:$A,0),1),SM ALL(IF(OFFSET($B$1,0,0,MATCH(TODAY(),$A:$A,0),1)=$ E$1,ROW(OFFSET($B$1,0,0,MATCH(TODAY(),$A:$A,0),1)) ),COUNTIF(OFFSET($B$1,0,0,MATCH(TODAY(),$A:$A,0),1 ),$E$1)+ROW(A1)-1))

'To return the next entry copy thebelow formula and copy to the right cell..
=INDEX(C:C,SMALL(IF($B$1:$B$65535=$E$1,ROW($B$1:$B $65535)),COUNTIF(OFFSET($B$1,0,0,MATCH(TODAY(),$A: $A,0),1),$E$1)+1))


If this post helps click Yes
---------------
Jacob Skaria


"Huggy" wrote:

I have a spreadsheet with column A being list of dates (from 1/1/08 - 31/12/10)
in order and column B a list of codes. Each codes periodically is repeasted
in no fixed order. Column C and D have data that relate to the code in column
B.

What I would like to do is based on todays date show the last time the code
was used before today along with the data in column B & C, then show the next
time the code is used from today onwards along with the data in column B & C.

Thanks for the help.

Try these...

list of dates (from 1/1/08 - 31/12/10) in order

Assumming the range of dates is A2:A1097

E2 = lookup code

For the last instance *before* today:

=LOOKUP(2,1/(B2:INDEX(B2:B1097,MATCH(NOW(),A2:A1097)-1)=E2),C2:INDEX(C2:C1097,MATCH(NOW(),A2:A1097)-1))

For the next instance:

=VLOOKUP(E2,C1097:INDEX(B2:B1097,MATCH(TODAY(),A2: A1097)),2,0)

--
Biff
Microsoft Excel MVP


"Huggy" wrote in message
...
I have a spreadsheet with column A being list of dates (from 1/1/08 -
31/12/10)
in order and column B a list of codes. Each codes periodically is
repeasted
in no fixed order. Column C and D have data that relate to the code in
column
B.

What I would like to do is based on todays date show the last time the
code
was used before today along with the data in column B & C, then show the
next
time the code is used from today onwards along with the data in column B &
C.

Thanks for the help.