+ 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
    12,570

    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
    Office 365
    Posts
    14,987

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

    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
    23

    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
    Office 365
    Posts
    14,987

    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
    Office 365
    Posts
    14,987

    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)

Similar Threads

  1. Pull specific data from the month selected from a list
    By excelentben in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-02-2017, 05:04 PM
  2. [SOLVED] Pull values after specific word
    By biznez in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-07-2015, 07:36 PM
  3. Pull Values from List Exluding Matching Values in Second List
    By sskgintl in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-16-2014, 09:29 PM
  4. Need to pull out specific info from a mixed list
    By JessicaE in forum Excel General
    Replies: 2
    Last Post: 04-03-2014, 05:52 AM
  5. Replies: 3
    Last Post: 11-26-2013, 02:34 PM
  6. Replies: 5
    Last Post: 11-06-2012, 04:56 PM
  7. Pull out specific values from numerous workbooks
    By jimmy1981 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 12-21-2010, 04:19 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