1. In the attached wbk I need a formula that will lookup the LAST (max) date in a given year (Cell B:1) and return the Yr-End-Balance (in cell B:2) for that date. The results in this case should be 62

Thanks

2. IF the F column contains the year and your dates are sorted ascending you can use the formula:

=INDEX(D610000,MATCH(B1+1,F6:F10000,0)-1)

3. If you do not have a column with the year you could use the array formula of:

=INDEX(D610000,MATCH(B1+1,YEAR(A6:A10000),0)-1)

formula needs to be confirmed with Ctrl+Shift+Enter

4. [quote name='mbarron' post='787264' date='03-Aug-2009 03:45']IF the F column contains the year and your dates are sorted ascending you can use the formula:

=INDEX(D610000,MATCH(B1+1,F6:F10000,0)-1)[/quote]

Thanks Mike. One small problem: when I change the Year to 2009, it fails. I think the formula is looking for 2010 in the Year col, which doesn't exist yet.
How can we tweak this?

Paul

5. You could use the following array formula (confirm with Ctrl+Shift+Enter):

=VLOOKUP(MAX(IF(YEAR(A6:A10)=B1,A6:A10)),A610,4)

6. [quote name='HansV' post='787275' date='03-Aug-2009 06:39']You could use the following array formula (confirm with Ctrl+Shift+Enter):

=VLOOKUP(MAX(IF(YEAR(A6:A10)=B1,A6:A10)),A610,4)[/quote]

The following is a bit cumbersome, but it also works and avoids the array formula (something I prefer).

=IF(ISERROR(MATCH(B1+1,F6:F10000,0)-1),INDEX(D610000,MATCH(B1,F6:F10000,1)),INDEX(D610000,MATCH(B1+1,F6:F10000,0
)-1))