0

I have a row in mid year which I want to get the tax value of the previous year-end row. Sometimes it will be year-12-31 and sometime there will not be that date, but a few days before it.

enter image description here

So in B17 I want to hold B12. There are dates for the last 10 years and only on YYYY-03-08 I want to hold the previous year end tax value.

I tried =MAX(IF(A2:A17<=DATE(YEAR(A17)-1,12,31),A2:A17)) And =MATCH(MAX(A2:A17-DATE(YEAR(A17)-1,12,31),0),A2:A17) no luck.

Improve this question
0

1 Answer 1

Reset to default
0

If your dates are in sorted order and you are able to use a third column, then flag when the year changes from one row to the next.

In cell C2 and copied down:
=IF(YEAR(A3)-YEAR(A2)=1,YEAR(A2),"")

In cell B17:
=INDEX(B2:B16,MATCH(YEAR(A17)-1,C2:C16,0),1)

The above formulas assume that the data is as presented in your image and was tested on Excel 2010.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .