+ Reply to Thread
Results 1 to 11 of 11

Index / Large Help

  1. #1
    Registered User
    Join Date
    05-14-2008
    Location
    Hong Kong
    MS-Off Ver
    2019
    Posts
    52

    Post Index / Large Help

    Hi there,

    I need some help with Large and Index function.

    In my spreadsheets, I have the columns that contain the team name and score and the row contain the week no.

    I can locate the top score and locate the week no. but I don't how to locate the Team name.

    Can you please advice what formula I should use?

    Please see attached sample: Highlighted in Yellow.

    Regards
    Ray
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2010
    Posts
    1,796

    Re: Index / Large Help

    Hi,

    in F19 and below


    Please Login or Register  to view this content.

    Regards
    -----------------------------------------------------

    At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.

    Please, mark your thread [SOLVED] if you received your answer.

  3. #3
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2010
    Posts
    1,796

    Re: Index / Large Help

    Hi,

    attached an example.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    05-14-2008
    Location
    Hong Kong
    MS-Off Ver
    2019
    Posts
    52

    Re: Index / Large Help

    Hi,

    Thanks but I just tried both and when I have 3 the same score, the team name don't show correctly.


    Team 01 Team 02 Team 03 Team 04 Team 05
    Week 01 2000 1741 1931 1686 1684
    Week 02 2000 2000 2000 1762 1817
    Week 03 1797 1841 1990 1936 1891



    Results

    Top 5
    Duplicate 1990
    Count 5

    Rank Score Week No. Team No.
    1 2000 Week 01 Team 01
    2 2000 Week 02 Team 01
    3 2000 Week 02 Team 01
    4 2000 Week 02 Team 01
    5 1990 Week 03 Team 03

  5. #5
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2010
    Posts
    1,796

    Re: Index / Large Help

    Hi

    you could exploit AGGREGATE (available from Excel 2010)


    =IFERROR(INDEX(D$5:H$5,AGGREGATE(15,6,COLUMN($D:$H)-COLUMN($D$1)+1/(INDEX($D$6:$H$8,MATCH(E19,C$6:C$8,0),0)=D19),COUNTIFS(D$19:D19,D19,F$19:F19,F19))),"")

    Maybe the red segment is too much prudent...


    Regards
    Last edited by canapone; 03-28-2017 at 02:52 AM.

  6. #6
    Registered User
    Join Date
    05-14-2008
    Location
    Hong Kong
    MS-Off Ver
    2019
    Posts
    52

    Re: Index / Large Help

    Hi Canapone,

    Sorry but I can't seems to get it right. can you put that into the sample that I sent?

    many Thanks
    Ray

  7. #7
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2010
    Posts
    1,796

    Re: Index / Large Help

    Hi,

    attached an example


    You are right: in the formula there was a typo...my apologies


    Please Login or Register  to view this content.

    Hope to have a better luck...

    Please refer to the file attached as I'm translating formulas from Italian settings.
    Attached Files Attached Files
    Last edited by canapone; 03-29-2017 at 05:00 AM.

  8. #8
    Valued Forum Contributor
    Join Date
    03-20-2011
    Location
    UK
    MS-Off Ver
    Excel 2007/10/16
    Posts
    840

    Re: Index / Large Help

    Hi

    This will be better formula for you instead using array formula!

    canapone formula is good?
    But this one is shorter formula

    E19
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Copy down
    F19
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Copy down

    See the file in highlight yellow

    Regards
    Attached Files Attached Files
    To help you by my post? it would be nice to click on to say "Thank you".
    If you are happy with a solution to your problem?
    Click Thread Tools above your first post,
    select "Mark your thread as Solved".

  9. #9
    Registered User
    Join Date
    05-14-2008
    Location
    Hong Kong
    MS-Off Ver
    2019
    Posts
    52

    Re: Index / Large Help

    Thank you both micope21 & Canapone - exactly what I needed.

  10. #10
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    20,264

    Re: Index / Large Help

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  11. #11
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2010
    Posts
    1,796

    Re: Index / Large Help

    Hi wpm7113,

    I suggest to shuffle around the numbers to test the formulas, or more simply try to write 2000 in E6 or in H8 to check outputs from formulas.


    In the attachment some examples.
    Attached Files Attached Files
    Last edited by canapone; 04-02-2017 at 01:49 PM.

+ 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. Index with large data
    By shhj in forum Excel General
    Replies: 2
    Last Post: 04-26-2016, 08:32 AM
  2. Index match with large function
    By morobo in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-19-2014, 02:27 PM
  3. Excel 2007 : INDEX LARGE with two conditions.
    By jvelez198 in forum Excel General
    Replies: 2
    Last Post: 02-20-2012, 08:26 AM
  4. INDEX, MATCH and LARGE
    By ridebikes in forum Excel General
    Replies: 1
    Last Post: 10-24-2011, 04:03 PM
  5. Index, Match, and Large
    By edwardpestian in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-03-2006, 06:10 PM
  6. LARGE and INDEX functions
    By bob in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-03-2006, 02:15 AM
  7. Large Index Match Lookup
    By Qaspec in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 07:05 AM

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