List by highest including Joint Highest

[no9kris]

My spreadsheet has details of various Teams which are identified in Col A. I then need to order by highest figure in Dec (December) Col D for each Team.

This I thought was easy but I am having trouble when there are 2 people who have the joint or equal highest figure in the Dec column. In the example XLSX attached this occurs in Team 102 (Vicky/Carla) both 109. With also Team 106 Pierre/Asha both 121.

What I would like help with is being to show the following Team, Rank, Name and Dec information to all people with the highest figure using the Dec column.

The information I have manually placed on Sheet of the sample spreadsheet attached, though the answer does not have to be on another Sheet.

I have been trying over the weekend with different formulas but can�t get them to work.

An advise as to how to sort this would be most welcome.

Chris

[Glenn Kennedy]

Still using Excel 2016??

[no9kris]

Can use O365 but didn't help

[Glenn Kennedy]

For Excel 2016, it is nigh-on impossible to get the format you want. This is probably as good as it gets. Seletc the team from the purple dropdown. Much easier with O365.

=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($B$2:$B$25)/(($A$2:$A$25=$L$2)*($D$2:$D$25=AGGREGATE(14,6,$D$2:$D$25/($A$2:$A$25=$L$2),1))),ROWS(L$5:L5))),"")

copied across and down. SEE FILE.

[no9kris]

Ooh yeah see that works via dropdown for each team. Is it possible to get complete list with O365.
BTW I have updated the original spreadsheet with just the bare information on Sheet 1 with output (if possible) on sheet 2. Left too much gubins In originally

[Glenn Kennedy]

For O365, delete ALL expected results first (to avoid a #SPILL error):

Please Login or Register  to view this content.

No copy/paste needed.

[no9kris]

That looks fantastic Glenn, will get my head around the answer but many thanks top guy.
Reckon you solved this

[Glenn Kennedy]

You're welcome. Thanks for letting us know that you got an answer.

---

Please take a moment and consider clicking the "Add Reputation" button at the foot of any of the posts of anyone who helped you reach a solution here today.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.