Find last number in column if year matches

[p45cal]

In the attached, formula at cell K15:
=INDEX(Sheet3!$U$2:$U$1905,MATCH(MAX(IF(Sheet3!$L$2:$L$1905=$C15,Sheet3!$K$ 2:$K$1905)),Sheet3!$K$2:$K$1905,0))
gives the result 920,011.
I've guessed that Pipe A is Pipe AQC in column U of sheet 3.
It actually returns the value in column U that is on the same row of the latest (max) date in column K where there's a 6 in column L.
You can copy the formula down.
Similar formulae in L15 and M15.
For confirmation, there's a pivot table at cell O14, but this returns the max value in column U (or V or X) where there's the same Year# in column L (it doesn't look at the dates at all).

It looks as if the data in Sheet3 has been put together from data elsewhere, with lots of calculations added. It would probably be a lot simpler and robust to get the data you want directly from these source data.