+ Reply to Thread
Results 1 to 7 of 7

Retrieving annual returins using lookup functions in big data lists.

  1. #1
    Registered User
    Join Date
    04-25-2017
    Location
    Sweden
    MS-Off Ver
    Microsoft Office 365 ProPlus Version 2002
    Posts
    26

    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.
    Attached Files Attached Files

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    5,660

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

    Please try at A4

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

  3. #3
    Registered User
    Join Date
    04-25-2017
    Location
    Sweden
    MS-Off Ver
    Microsoft Office 365 ProPlus Version 2002
    Posts
    26

    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. #4
    Registered User
    Join Date
    04-25-2017
    Location
    Sweden
    MS-Off Ver
    Microsoft Office 365 ProPlus Version 2002
    Posts
    26

    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. #5
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    5,660

    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
    Attached Files Attached Files
    Last edited by Bo_Ry; 11-16-2020 at 01:43 AM.

  6. #6
    Registered User
    Join Date
    04-25-2017
    Location
    Sweden
    MS-Off Ver
    Microsoft Office 365 ProPlus Version 2002
    Posts
    26

    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.
    Last edited by Peter Niklas; 11-16-2020 at 04:56 PM.

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,936

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

    Quote Originally Posted by Peter Niklas View Post
    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: copy to clipboard
    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.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Retrieving Data from a report using matching/lookup/indexing/something else?
    By accountant123 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-09-2017, 08:58 AM
  2. Retrieving Cell Data and Reverse Lookup in separate spreadsheet
    By countdredd in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-01-2011, 06:45 AM
  3. LOOKUP / Retrieving Specific Cell (Auditing data)
    By HRGirl in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-26-2011, 04:57 PM
  4. Replies: 9
    Last Post: 07-15-2010, 11:59 AM
  5. lookup? retrieving entire row of data...
    By tfleming421 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-28-2007, 11:18 AM
  6. [SOLVED] annual increases in price lists
    By smedegaard in forum Excel General
    Replies: 1
    Last Post: 02-17-2006, 09:00 AM
  7. Retrieving Each Second Number from Two Lists
    By ExcelTip in forum Tips and Tutorials
    Replies: 0
    Last Post: 08-25-2005, 02:15 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1