choose data based on dates

[MoneyPlanters]

A B C D E F

Ticker Date/Time Open High Low Close
CAC40 1/2/2008 5609.98 5665.94 5530.43 5550.36
CAC40 1/3/2008 5538.2 5559.05 5515.61 5546.08
CAC40 1/4/2008 5543.69 5567.09 5417.53 5446.79
CAC40 1/7/2008 5432 5475.25 5429.32 5452.83
CAC40 1/8/2008 5476.51 5533.93 5471.14 5495.67
CAC40 1/9/2008 5459.4 5463.07 5420.06 5435.42
CAC40 1/10/2008 5468.54 5471.82 5394.52 5400.43
CAC40 1/11/2008 5398.49 5410.91 5349.25 5371.41
CAC40 1/14/2008 5345.83 5421.22 5343.25 5403.51
CAC40 1/15/2008 5390.37 5394.14 5250.09 5250.82
CAC40 1/16/2008 5216.22 5289.05 5177.68 5225.39
CAC40 1/17/2008 5278.29 5278.5 5150.24 5157.09
CAC40 1/18/2008 5159.56 5231.56 5081.23 5092.4


Column B contains dates and I need to pick data from columns C,D,E and F from the row which contains the latest date. How can this be achieved ? For example, K4, L4, M4 and N4 automatically gets filled with the data from columns C,D,E and F respectively which correspond to the latest date in column B.

 

[Aladin Akyurek]

A B C D E F

Ticker Date/Time Open High Low Close
CAC40 1/2/2008 5609.98 5665.94 5530.43 5550.36
CAC40 1/3/2008 5538.2 5559.05 5515.61 5546.08
CAC40 1/4/2008 5543.69 5567.09 5417.53 5446.79
CAC40 1/7/2008 5432 5475.25 5429.32 5452.83
CAC40 1/8/2008 5476.51 5533.93 5471.14 5495.67
CAC40 1/9/2008 5459.4 5463.07 5420.06 5435.42
CAC40 1/10/2008 5468.54 5471.82 5394.52 5400.43
CAC40 1/11/2008 5398.49 5410.91 5349.25 5371.41
CAC40 1/14/2008 5345.83 5421.22 5343.25 5403.51
CAC40 1/15/2008 5390.37 5394.14 5250.09 5250.82
CAC40 1/16/2008 5216.22 5289.05 5177.68 5225.39
CAC40 1/17/2008 5278.29 5278.5 5150.24 5157.09
CAC40 1/18/2008 5159.56 5231.56 5081.23 5092.4


Column B contains dates and I need to pick data from columns C,D,E and F from the row which contains the latest date. How can this be achieved ? For example, K4, L4, M4 and N4 automatically gets filled with the data from columns C,D,E and F respectively which correspond to the latest date in column B.

If the dates in column B are in ascending order...

J4:

=MATCH(9.99999999999999E+307,$B$2:$B$14)

K4, copy across to N4:

=INDEX(C$2:C$14,$J$4)

 

[MoneyPlanters]

Could you please explain the logic ? That will add to my understanding.

 

[Aladin Akyurek]

Could you please explain the logic ? That will add to my understanding.

The MATCH formula with the big number (see "limits" in Excel's Help) returns
the position of the last numeric value from the date range in column B. The following link
describes how it works:

http://www.mrexcel.com/forum/showthread.php?t=310278 (post #7)

The Index formula fetches the corresponding values from the associated ranges
at the calculated position.