# Retrieving annual returins using lookup functions in big data lists.

1. ## 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.

2. ## Re: Retrieving annual returins using lookup functions in big data lists.

=LOOKUP(DATE(\$D4;12;31);\$B\$12:C\$59)/INDEX(\$C\$12:\$C\$59;MATCH(\$D4;INDEX(YEAR(\$B\$12:\$B\$59););))-1

3. ## Re: Retrieving annual returins using lookup functions in big data lists.

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.

4. ## Re: Retrieving annual returins using lookup functions in big data lists.

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.

5. ## Re: Retrieving annual returins using lookup functions in big data lists.

LOOKUP function use approximate match for equal or lower value
Try this at M4
=LOOKUP(DATE(\$D4;12;31);\$B\$12:B\$59)

It always returns the last date of every year

6. ## Re: Retrieving annual returins using lookup functions in big data lists.

Actually I've notcied the function does not work. It only works for the 3 rows under A and not B & C. The problem is in the numerator it takes the wrong values.

Regarding finding the last year function it works fine! Thx.

7. ## Re: Retrieving annual returins using lookup functions in big data lists.

Originally Posted by Peter Niklas
Actually I've notcied the function does not work. It only works for the 3 rows under A and not B & C. The problem is in the numerator it takes the wrong values.
Try unlocking the column references in the array argument of the INDEX function, so that the formula reads:
Formula:
`Please Login or Register  to view this content.`

Paste the above into cell E4 and then drag the fill handle over and down to cell G7.
Let us know if you have any questions.

There are currently 1 users browsing this thread. (0 members and 1 guests)