Return text from Column A based on Rank of Column B which has duplicates

[bocajnr10]

hello,
I have a Basketball Stats sheet, and I would like to be able to create a section on the same sheet that lists the player initials based on the rank of their stat in a given criteria/column.
Per the below screenshot as an example, I have 17 players listed in Column A.
Columns B, C & D are examples of the Stat Types (in this case, respectively their Attempts, Makes & successful %).

My Stat Types are predominantly of integers, decimals or percentage (although positive & negatives values are a possibility). Importantly, duplicate values are common.

Objective: In the section below each Stat Type (called Rank within Team as seen in the screenshot) I want to be able to list each players initials in order based on the rank of that given Stat Type.
So as you can see below, NT is first based on 2PA and 2PM, but IOP is first based on 2P% (as expected).
The Stat Type of greatest importance to me will typically be the % column, and it would be great to be able to use the 2PA and/or 2PM values as additional criteria to break ties on duplicates found in 2P% (i.e. the higher 2PA or 2PM would rank higher in the tie breaker).

Now I did find on MrExcel the following formula that almost does what I need...
=INDEX(A:A,MATCH(LARGE(B:B,1),B:B,0)) -- (as found on Return text value from Col A, based on Rank From Col B)
... but it does not manage/persist with duplicate values.
That is, the LARGE function continues to return the text based on the first True value it finds, and ignores the duplicates.

The below screenshot leverages the above suggested formula, and in the spreadsheet, cell B21 below has the following adaptation --> =INDEX($A$2:$A$18,MATCH(LARGE(B$2:B$18,$A21),B$2:B$18,0))

Request:
1. Guidance on how to enhance =INDEX($A$2:$A$18,MATCH(LARGE(B$2:B$18,$A21),B$2:B$18,0)) so that it will manage/persist through duplicates.
and/or
2. In case I'm over complicating this, advise on a better way/formula list the initials in the sub-section "Rank within Team" based on their rank within the given Stat Type, including tie-breaking formula (perhaps with something like COUNTIFS ?)

thanks!

 

[Fluff]

Hi & welcome to MrExcel.

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also can you post some sample data, rather than an image.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[bocajnr10]

Hi & welcome to MrExcel.

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also can you post some sample data, rather than an image.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Thanks for the suggestion Fluff.
I'm using Office/Excel 2016, and usually on Windows (but occasionally on Mac) -- profile updated accordingly.

I didn't do the XL2BB Add-in simply because I did not think this PC (work constraints) was likely to allow me to install the Add-in. Hence why I went the image and copy/pasting of a formula from cell B21 of my spreadsheet. But will check out XL2BB just in case I am able to use it.

 

[Fluff]

Ok, how about

Fluff.xlsm
	A	B
1
2	A	11
3	B	61
4	C	11
5	D	111
6	E	41
7	F	90
8	G	30
9	H	38
10	I	24
11	J	33
12	K	25
13	L	10
14	M	18
15	N	5
16	O	1
17	P	4
18	Q	1
19
20	#	2PA
21	1	D
22	2	F
23	3	B
24	4	E
25	5	H
26	6	J
27	7	G
28	8	K
29	9	I
30	10	M
31	11	A
32	12	C
33	13	L
34	14	N
35	15	P
36	16	O
37	17	Q
Data

Cell Formulas
Range	Formula
B21:B37	B21	=INDEX($A$2:$A$18,AGGREGATE(15,6,(ROW($A$2:$A$18)-ROW($A$2)+1)/($B$2:$B$18=LARGE($B$2:$B$18,A21))/(ISNA(MATCH($A$2:$A$18,B$20:B20,0))),1))

 

[bocajnr10]

Ok, how about

Fluff.xlsm
	A	B
1
2	A	11
3	B	61
4	C	11
5	D	111
6	E	41
7	F	90
8	G	30
9	H	38
10	I	24
11	J	33
12	K	25
13	L	10
14	M	18
15	N	5
16	O	1
17	P	4
18	Q	1
19
20	#	2PA
21	1	D
22	2	F
23	3	B
24	4	E
25	5	H
26	6	J
27	7	G
28	8	K
29	9	I
30	10	M
31	11	A
32	12	C
33	13	L
34	14	N
35	15	P
36	16	O
37	17	Q
Data

Cell Formulas
Range	Formula
B21:B37	B21	=INDEX($A$2:$A$18,AGGREGATE(15,6,(ROW($A$2:$A$18)-ROW($A$2)+1)/($B$2:$B$18=LARGE($B$2:$B$18,A21))/(ISNA(MATCH($A$2:$A$18,B$20:B20,0))),1))

Thanks very much Fluff!!
I've just plugged that into my spreadsheet, across all the Stat Types I have (35 of them) and it seems to have done the job nicely.
I'll review properly in the morning (as it's quite late here), but in the mean time, thank you again!!

 

[Fluff]

You're welcome & thanks for the feedback.