Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Attached Files Attached Files

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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. #3
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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. #4
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [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. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You could use the following array formula (confirm with Ctrl+Shift+Enter):

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

  6. #6
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [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))

    Thanks for your help, guys.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Or even simpler

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •