Look up last and next entry

[Huggy]

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.

 

[Jacob Skaria]

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),SMALL(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

 

[T. Valko]

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)

 

[Huggy]

Thanks for the help. Not quite what I was after. I'll try and make my
question clearer.

Exapmple of the data I have is; (I keep adding to this list of dates each
year)

Date Code Description 1 Delivery Date
20/10 SN Swan 28/10
21/10 DR Dream 30/10
25/10 PR Pioneer 10/11
29/10 ST Swift 11/11
2/11 SN Swan 10/11
3/11 SN Swan 28/11
5/11 PR Pioneer 10/12
29/11 ST Swift 11/12
30/11 DR Dream 9/12

I would like based on above data and starting from todays date complete a
table which shows the last time and the next time the codes are used.

Example table; which has the codes listed already for lookup reference. The
date and delivery date data is what I need to lookup from the data above.
Only the last time the code was used and the next time it will be used, then
bring the result and show on the table below

----last time used--- ----next time used-----
Code Date Delivery date Date Delivery date
PR 25/10 10/11 5/11 10/12
DR 21/10 30/10 30/11 9/12
SN 20/10 28/10 3/11 28/11
ST 29/10 11/11 29/11 11/12

Hope this is a little clearer.

Thanks again.

 

[Jacob Skaria]

--I tried with your data in ColA to ColD..headers in Row1

--In the same sheet in from ColF to ColJ I have setup the headers in row1
and colF

Col F Col G Col H Col I Col J
Code Date L Del date Date N Del date
PR 29-Oct-09 11-Nov 5-Nov 12-Oct
DR 25-Oct-09 11-Oct 30-Nov 12-Sep
SN 21-Oct-09 30/10 2-Nov 11-Oct


--Try these formulas in row 2 and copy down as required..All are array
formulas. Use Ctrl+Shift+Enter..If no match is found #NUM error is
returned..You will need to handle this..

In G
=INDEX(OFFSET(A$1,0,0,MATCH(TODAY(),$A:$A,1),1),SMALL(IF(OFFSET($B$1,0,0,MATCH(TODAY(),$A:$A,1),1)=$F2,ROW(OFFSET($B$1,0,0,MATCH(TODAY(),$A:$A,1),1))),COUNTIF(OFFSET($B$1,0,0,MATCH(TODAY(),$A:$A,1),1),$F2)))


In H
=INDEX(OFFSET(D$1,0,0,MATCH(TODAY(),$A:$A,1),1),SMALL(IF(OFFSET($B$1,0,0,MATCH(TODAY(),$A:$A,1),1)=$F2,ROW(OFFSET($B$1,0,0,MATCH(TODAY(),$A:$A,1),1))),COUNTIF(OFFSET($B$1,0,0,MATCH(TODAY(),$A:$A,1),1),$F2)))

In I
=INDEX(A:A,SMALL(IF($B$1:$B$65535=$F2,ROW($B$1:$B$65535)),COUNTIF(OFFSET($B$1,0,0,MATCH(TODAY(),$A:$A,1),1),$F2)+1))

In J
=INDEX(D,SMALL(IF($B$1:$B$65535=$F2,ROW($B$1:$B$65535)),COUNTIF(OFFSET($B$1,0,0,MATCH(TODAY(),$A:$A,1),1),$F2)+1))



If this post helps click Yes

 

[Huggy]

Hi Jacob,

Looks excellent. However, could you please check the code you have in G2. if
I select the code that is at the top of the list if return with the header
and if I select a code that is in the middle of the list it returns with the
one above it.

All the other codes work fine.

Thanks
Mike

 

[Huggy]

Hi Jacob,

Thanks for your help. Looks excellent. However could you please check the
code you have in cell G2. It returns the header of column A or a date earlier
than the related code in column F. I copied and pasted the code you provided,
so I don't have any typos.

All the other codes work fine.

Thanks again

 

[Huggy]

Hi Jacob,

Please disregard my note below. It all works. With all the copying and
changing I forgot to CTRL+SHIFT+ENTER to enter the formula.

Great formula. Does exactly what I need.
Thank you.