=max

Good Evening

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

have this little formula

=MAX(Leaderboard!B2:B104)

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

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

Thanks.

hopefully attached, have had to zip it down

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

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 ?

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

Originally Posted by belinda200

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

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

Originally Posted by belinda200

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

if at all possible

Originally Posted by belinda200

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

Select F11:F13 area to input array formula

HTML Code: 

You're welcome, thanks for the feedback.