Lookup last date and return with result

[DHayes]

Good day,
I am struggling to put a formula together. I need to get the opening and closing from sheet1 (Below) and put it into sheet2. I need the balance from the last date but not later 2015-03-31. Some accounts the last date is in 2014. I need to do this for +- 23000 accounts. any assistance will be appreciated.

[TABLE="width: 475"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Invoice_Date[/TD]
[TD][/TD]
[TD]Account_No[/TD]
[TD][/TD]
[TD]Opening_Balance[/TD]
[TD][/TD]
[TD]Current_Balance[/TD]
[/TR]
[TR]
[TD]2014-01-16[/TD]
[TD][/TD]
[TD]100137/000239[/TD]
[TD][/TD]
[TD]208.62[/TD]
[TD][/TD]
[TD]418.90[/TD]
[/TR]
[TR]
[TD]2014-02-17[/TD]
[TD][/TD]
[TD]100137/000239[/TD]
[TD][/TD]
[TD]418.90[/TD]
[TD][/TD]
[TD]630.80[/TD]
[/TR]
[TR]
[TD]2014-03-18[/TD]
[TD][/TD]
[TD]100137/000239[/TD]
[TD][/TD]
[TD]630.83[/TD]
[TD][/TD]
[TD]208.60[/TD]
[/TR]
[TR]
[TD]2014-04-16[/TD]
[TD][/TD]
[TD]100137/000239[/TD]
[TD][/TD]
[TD]208.62[/TD]
[TD][/TD]
[TD]418.90[/TD]
[/TR]
[TR]
[TD]2014-05-19[/TD]
[TD][/TD]
[TD]100137/000239[/TD]
[TD][/TD]
[TD]418.90[/TD]
[TD][/TD]
[TD]630.80[/TD]
[/TR]
[TR]
[TD]2014-06-25[/TD]
[TD][/TD]
[TD]100137/000239[/TD]
[TD][/TD]
[TD]630.83[/TD]
[TD][/TD]
[TD]844.40[/TD]
[/TR]
[TR]
[TD]2014-07-29[/TD]
[TD][/TD]
[TD]100137/000239[/TD]
[TD][/TD]
[TD]844.40[/TD]
[TD][/TD]
[TD]1989.00[/TD]
[/TR]
[TR]
[TD]2014-08-24[/TD]
[TD][/TD]
[TD]100137/000239[/TD]
[TD][/TD]
[TD]1989.02[/TD]
[TD][/TD]
[TD]3136.10[/TD]
[/TR]
[TR]
[TD]2014-09-18[/TD]
[TD][/TD]
[TD]100137/000239[/TD]
[TD][/TD]
[TD]3136.11[/TD]
[TD][/TD]
[TD]4300.70[/TD]
[/TR]
[TR]
[TD]2014-10-20[/TD]
[TD][/TD]
[TD]100137/000239[/TD]
[TD][/TD]
[TD]4300.70[/TD]
[TD][/TD]
[TD]11845.20[/TD]
[/TR]
[TR]
[TD]2014-11-17[/TD]
[TD][/TD]
[TD]100137/000239[/TD]
[TD][/TD]
[TD]11845.21[/TD]
[TD][/TD]
[TD]12173.60[/TD]
[/TR]
[TR]
[TD]2014-12-17[/TD]
[TD][/TD]
[TD]100137/000239[/TD]
[TD][/TD]
[TD]12173.63[/TD]
[TD][/TD]
[TD]12504.00[/TD]
[/TR]
[TR]
[TD]2015-01-20[/TD]
[TD][/TD]
[TD]100137/000239[/TD]
[TD][/TD]
[TD]12504.00[/TD]
[TD][/TD]
[TD]12836.30[/TD]
[/TR]
[TR]
[TD]2015-02-17[/TD]
[TD][/TD]
[TD]100137/000239[/TD]
[TD][/TD]
[TD]12836.32[/TD]
[TD][/TD]
[TD]13170.55[/TD]
[/TR]
[TR]
[TD]2015-03-19[/TD]
[TD][/TD]
[TD]100137/000239[/TD]
[TD][/TD]
[TD]13170.59[/TD]
[TD][/TD]
[TD]13506.75[/TD]
[/TR]
[TR]
[TD]2015-04-19[/TD]
[TD][/TD]
[TD]100137/000239[/TD]
[TD][/TD]
[TD]13506.79[/TD]
[TD][/TD]
[TD]13844.90[/TD]
[/TR]
[TR]
[TD]2015-05-18[/TD]
[TD][/TD]
[TD]100137/000239[/TD]
[TD][/TD]
[TD]13844.94[/TD]
[TD][/TD]
[TD]4174.75[/TD]
[/TR]
[TR]
[TD]2015-06-28[/TD]
[TD][/TD]
[TD]100137/000239[/TD]
[TD][/TD]
[TD]4174.76[/TD]
[TD][/TD]
[TD]5346.00[/TD]
[/TR]
[TR]
[TD]2015-07-24[/TD]
[TD][/TD]
[TD]100137/000239[/TD]
[TD][/TD]
[TD]5346.04[/TD]
[TD][/TD]
[TD]6550.05[/TD]
[/TR]
[TR]
[TD]2015-08-19[/TD]
[TD][/TD]
[TD]100137/000239[/TD]
[TD][/TD]
[TD]6550.05[/TD]
[TD][/TD]
[TD]7765.25[/TD]
[/TR]
[TR]
[TD]2015-09-20[/TD]
[TD][/TD]
[TD]100137/000239[/TD]
[TD][/TD]
[TD]7765.25[/TD]
[TD][/TD]
[TD]8990.60[/TD]
[/TR]
</tbody>[/TABLE]

 

[Lapwing]

If the data is always in chronological order and the last transaction is in the final row; and there is only one account per tab then this will work: =LOOKUP(2,1/(A:A<>""),C:C)
Change C:C to D:D for the Current Balance.

 

[Lapwing]

Just noticed your restriction " but not later 2015-03-31." so my formula will not work.

This should do the trick: =LOOKUP(42094,A:A,C:C)
You could change the 42094 to a cell reference that had the max date in it.