Finding Oldest Year in a series of Dates then find value in row below last day of the year

This one is probably pretty simple for you people, but I cannot figure it out.

Using the sample spreadsheet attached. I have a series of last days of a month, 60 in all cells 5A:5BH. Below those dates are dollar values.

I want to figure out what the oldest year is on the series and put that year in cell 5BK. Then increment that cell by 1 year for cells 5BL:5BO then below that row put the value found below the day of the year 12/31/yyyy (6A:6BH) in row 6 into 6BK:6BO corresponding to the year in cells 5BK:5BO. The desired results are shown in the attached example.

before that can be done you have a problem that your dates are text, if this is truly representative of your data your "dates" in row 5 aren't dates but text. if you want to convert them to dates, copy an empty cell then highlight A5:BH5 then hit paste special and click on Add and ok and they'll be converted to dates then format as you want.
Still working on a formula for the rest.

so, do you know your oldest year in the series or does that need to be calculated? Because if you already know your oldest year then that is a different formula than a formula to determine what year is the oldest in the list?
For example, if I'm just trying to give you a sum of all the 2014 year values in row 6 then I'd use a sumproduct formula but if I have to figure out what the oldest year is in the list then I'd probably use a min/if formula.
in other words, are you looking to populate BK5 with the oldest year OR are you looking to populate BK6 with the sum of values FOR the year in BK5?

yes, much clearer. I have to work on a different formula.

I had this sumproduct formula that would return the value for each year in BK5 through BO5... =SUMPRODUCT((YEAR($A$5:$BH$5)=BK5)*($A$6:$BH$6))

But, for your problem as you outlined it in post #5, if the values in cells BK5 through BO5 were changed to the end of each year, like 12/31/2014 and 12/31/2015 but formatted to look like years it would be a simple hlookup to return the year end value. HOWEVER that would make the above sumproduct ineffectual because that uses year and not individual date. something like a helper row starting with 12/31/2014, then in the next column =EDATE(BK4,12) and drag it right (I started it in BK4).
then this for the lookup... =HLOOKUP(BK4,$A$5:$BH$6,2,FALSE) and dragged right would work.

Still thinking about a max and an offset or some other combination.

as a follow up to post #8, I find helper cells to be... very helpful (yes redundant). Some people frown on them because they are looking for elaborate all in one formulas but I find them invaluable. I often use them as it quite often simplifies the downstream formulas. AND, if I would be worried about it being seen, I just change the font color to the same as the cell background color (white in most cases) and they become invisible.
Still thinking about a different other formula for now though. Maybe in the mean time someone else will stop by with a different formula.

Another solution.

BK5

Please Login or Register  to view this content.

Press Ctrl-Shift-Enter for Array formula

BK6

Please Login or Register  to view this content.

copy to right side as you want


Regards.