Excel - Look up last and next entry

Asked By Huggy on 25-Oct-09 11:33 PM
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 replied on 26-Oct-09 12:38 AM
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
---------------
Jacob Skaria
T. Valko replied on 26-Oct-09 01:18 AM
Try these...


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 replied on 26-Oct-09 02:16 AM
Thanks for the help. Not quite what I was after. I will 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 replied on 26-Oct-09 02:48 AM
--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 G2
=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 H2
=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 I2
=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 J2
=INDEX(D: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
---------------
Jacob Skaria
Huggy replied on 26-Oct-09 05:53 AM
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 replied on 26-Oct-09 06:04 AM
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 do not have any typos.

All the other codes work fine.

Thanks again
Huggy replied on 26-Oct-09 06:45 AM
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.