Retrieving annual returins using lookup functions in big data lists.

Hello,

I have made a sample excel to illustrate what I want to accomplish.

I want to find the return for company A,B,C for the specific years: 2017,2018 & 2019.

My thoughts:

I want to find a function that the latest value in 2017 and divides it with the earliest value in 2017 and subtract 1.
Formula: (C24/C12 -1) for 2017 for instance.

The strategy i initially had was to create two LOOKUP() functions that i use to divide to get the return. However, i cannot figure out how to make the lookup function take the last value of a year.

Please comment if I need to clarify more.

Hello,

Thank you for spending the time to respong to my thread.

The code you made works. However, in the big data set im using the last day of a year that I have data on is not always on 31st of December. Sometimes the last day is 27th of december for instance. Due to holidays etc.

Is it possible to have the exakt same code but with another way of refering to the last day in a year?

I was thinking of maybe there is a function that find the MAX value between "2017.01.01 and 2017.12.31". However, I could find such a function.

Hello,

Thank you for spending the time to respong to my thread.

The code you made works. However, in the big data set im using the last day of a year that I have data on is not always on 31st of December. Sometimes the last day is 27th of december for instance. Due to holidays etc.

Is it possible to have the exakt same code but with another way of refering to the last day in a year?

I was thinking of maybe there is a function that find the MAX value between "2017.01.01 and 2017.12.31". However, I could find such a function.