+ Reply to Thread
Results 1 to 13 of 13

=max

  1. #1
    Forum Contributor murray83's Avatar
    Join Date
    05-05-2016
    Location
    Daventry
    MS-Off Ver
    365
    Posts
    146

    =max

    Good Evening

    have a question I'm sure you lot may know the answer to

    have this little formula
    Please Login or Register  to view this content.
    which works fine and pulls the max figure fine and dandy, but i would also like it to also pull a name which is associated with that score , attached is my layout

    excel.png

    cheers one and all

  2. #2
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365
    Posts
    3,187

    Re: =max

    Pull the name from where?
    Please post the excel file.

    Thanks.

  3. #3
    Forum Contributor murray83's Avatar
    Join Date
    05-05-2016
    Location
    Daventry
    MS-Off Ver
    365
    Posts
    146

    Re: =max

    hopefully attached, have had to zip it down
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365
    Posts
    3,187

    Re: =max

    Try this in the name field:
    =INDEX(Leaderboard!B:B,MATCH(F23,Leaderboard!A:A,0))

  5. #5
    Forum Contributor murray83's Avatar
    Join Date
    05-05-2016
    Location
    Daventry
    MS-Off Ver
    365
    Posts
    146

    Re: =max

    Clever, clever people

    many thanks

    but just a little edit

    i know the index and mathc only fetches back first instacne so if two people score same only show based on sorting of names by alphanumeric, but is there a function that shows each, sort of concenates them ?

  6. #6
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365
    Posts
    3,187

    Re: =max

    hmm....can you post an example? and add the results expected manually? I think it would be more comprehensible with a demonstration.
    Thanks.

  7. #7
    Forum Contributor murray83's Avatar
    Join Date
    05-05-2016
    Location
    Daventry
    MS-Off Ver
    365
    Posts
    146

    Re: =max

    Quote Originally Posted by belinda200 View Post
    hmm....can you post an example? and add the results expected manually? I think it would be more comprehensible with a demonstration.
    Thanks.
    of course, have just added a few more scores and names and a filter so can see what i mean by the name order
    Attached Files Attached Files

  8. #8
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365
    Posts
    3,187

    Re: =max

    OK, and you want all 3 names that scored 18 to appear on the name field?

  9. #9
    Forum Contributor murray83's Avatar
    Join Date
    05-05-2016
    Location
    Daventry
    MS-Off Ver
    365
    Posts
    146

    Re: =max

    Quote Originally Posted by belinda200 View Post
    OK, and you want all 3 names that scored 18 to appear on the name field?
    if at all possible

  10. #10
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365
    Posts
    3,187

    Re: =max

    How about seperating it to 3 cells like in the attached?

    F11 and down:
    =IFERROR(INDEX(Leaderboard!$B$1:$B$7,AGGREGATE(14,6,(ROW(Leaderboard!$B$2:$B$7)/(Leaderboard!$A$2:$A$7=$F$21)),ROWS($A$1:A1))),"")
    Attached Files Attached Files
    Last edited by Limor_OP; 09-17-2020 at 05:25 PM.

  11. #11
    Forum Contributor murray83's Avatar
    Join Date
    05-05-2016
    Location
    Daventry
    MS-Off Ver
    365
    Posts
    146

    Re: =max

    Quote Originally Posted by belinda200 View Post
    How about seperating it to 3 cells like in the attached?

    F11 and down:
    =IFERROR(INDEX(Leaderboard!$B$1:$B$7,AGGREGATE(14,6,(ROW(Leaderboard!$B$2:$B$7)/(Leaderboard!$A$2:$A$7=$F$21)),ROWS($A$1:A1))),"")
    very good thank you and good night

  12. #12
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    China Shanghai
    MS-Off Ver
    365 V2503 and WPS V2024(12.1.0.18543)
    Posts
    3,927

    Re: =max

    Select F11:F13 area to input array formula
    HTML Code: 

  13. #13
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365
    Posts
    3,187

    Re: =max

    You're welcome, thanks for the feedback.

+ 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