+ Reply to Thread
Results 1 to 7 of 7

Pull Top Three Values in List Only If in Specific Year

  1. #1
    Registered User
    Join Date
    05-23-2020
    Location
    New York, NY
    MS-Off Ver
    Excel for Mac Version 16.34 Office 365
    Posts
    1

    Unhappy Pull Top Three Values in List Only If in Specific Year

    I am trying to automatically pull in the top three values per year.
    Based on the number of webinar registrations, pull in the top 3 webinars -> title and number of registrations.
    I've included a small sample set. This gets updated daily with multiple new events, and this will allow us to in. real time see the top 3 events.

    The formula I am currently using is:
    Rank 1: =INDEX(A17:A300,MATCH(LARGE(E17:E300,1),E17:E300,0))
    Rank 2: =INDEX(A17:A300,MATCH(LARGE(E17:E300,2),E17:E300,0))
    Rank 3: =INDEX(A17:A300,MATCH(LARGE(E17:E300,3),E17:E300,0))

    This works to pull in the top three overall, but it doesn't work to pull in only 2019, or only 2020.

    It is the text in red I am trying to write formulas for, based on the data set below the red text.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    10,832

    Re: Pull Top Three Values in List Only If in Specific Year

    E5:
    Please Login or Register  to view this content.
    f5:
    Please Login or Register  to view this content.
    Ben Van Johnson

  3. #3
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    12,851

    Re: Pull Top Three Values in List Only If in Specific Year

    With a pivot table.

    See column J and K.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  4. #4
    Forum Expert Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    Office 365
    Posts
    3,456

    Re: Pull Top Three Values in List Only If in Specific Year

    Please try at D5

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Copy to D12

    Change number in Blue for another top rank.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    05-23-2020
    Location
    Guwahati, Assam, India
    MS-Off Ver
    2019
    Posts
    16

    Re: Pull Top Three Values in List Only If in Specific Year

    Asha Kanta Sharma
    Guwahati, India

  6. #6
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    12,851

    Re: Pull Top Three Values in List Only If in Specific Year

    @ashakantasharma

    Thanks for your contribution.

    It is common use, you also add the solution, or even better the formula, in the text of the reply.

    In that cases forummembers don't have to open you excel file, to see what solution is been offered.

    See e.g. #4 of Bo_Ry

    That is also usefull, if a member can't open the excel file (like me in this case).

  7. #7
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    12,851

    Re: Pull Top Three Values in List Only If in Specific Year

    Thanks for the rep.

    Glad I could help.

    If you are satisfied with the solution, please mark the question solved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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