Need to Return Unique Players for Same Multiple Number Result (XLOOKUP?)

[chasfh]

I am doing a dynamic table to query a list of 672 MLB (baseball) players and return all those players in each league (i.e., American League, National League) who have hit enough home runs (also, "homers") to qualify for a top ten list for each league.

There will be multiple players tied on the list with the same number of homers. There may also be more than ten players listed for at least one of the leagues because enough multiple players are tied to take the list beyond ten.

The problem (as you can surmise) is that the query I wrote is returning the same name for each instance where there is a tie. For example, here is the top ten homer hitters for the National League:

Player	HR
Austin Riley ATL	36
Paul Goldschmidt STL	35
Paul Goldschmidt STL	35
Mookie Betts LAD	34
Christian Walker ARI	32
Rowdy Tellez MIL	30
Nolan Arenado STL	29
Manny Machado SDP	28
Manny Machado SDP	28
Manny Machado SDP	28
Manny Machado SDP	28
Manny Machado SDP	28

There should be twelve different players (i.e., unique vales) listed with each homer total.

The problem starts at the second and third positions. Two players are tied for second with 35, but the way I wrote my XLOOKUP, it is returning the same name, Paul Goldschmidt STL, for both second and third most, whereas the name in the third slot should be Pete Alonso NYM.

Same when we get to the eighth through twelfth slots. Five different guys are tied for eighth at 28, and Manny Machado SDP is listed in all five slots, and while he does belong in one of them, the other four should be occupied by C.J. Cron COL, Rhys Hoskins PHI, Matt Olson ATL, and Willy Adames MIL.

I've googled around and saw something about using FILTER to return unique values in a list, but I am unclear about how that might work in a case like this.

I have pasted below the Mini-Sheet for each of the two tabs in question.

I appreciate your consideration. Thank you.



Tab 1:

Excceljet HR Examples.xlsx
	B	C
3	HOME RUNS
4	Player	HR
5	Aaron Judge NYY	57
6	Kyle Schwarber PHI	38
7	Mike Trout LAA	35
8	Shohei Ohtani LAA	34
9	Yordan Alvarez HOU	33
10	Eugenio Suarez SEA	31
11	Corey Seager TEX	30
12	Corey Seager TEX	30
13	Vladimir Guerrero Jr. TOR	28
14	Vladimir Guerrero Jr. TOR	28
15
16
17
18
19
20
21
22
23	HOME RUNS
24	Player	HR
25	Austin Riley ATL	36
26	Paul Goldschmidt STL	35
27	Paul Goldschmidt STL	35
28	Mookie Betts LAD	34
29	Christian Walker ARI	32
30	Rowdy Tellez MIL	30
31	Nolan Arenado STL	29
32	Manny Machado SDP	28
33	Manny Machado SDP	28
34	Manny Machado SDP	28
35	Manny Machado SDP	28
36	Manny Machado SDP	28
37
38
39
League Leaders

Cell Formulas
Range	Formula
B5:B19	B5	=XLOOKUP(C5&"AL",'Batting Standard'!J:J&'Batting Standard'!X:X,'Batting Standard'!Z:Z,"")
C5	C5	=LARGE(IF('Batting Standard'!X:X="aL",'Batting Standard'!J:J),1)
C6	C6	=LARGE(IF('Batting Standard'!X:X="aL",'Batting Standard'!J:J),2)
C7	C7	=LARGE(IF('Batting Standard'!X:X="aL",'Batting Standard'!J:J),3)
C8	C8	=LARGE(IF('Batting Standard'!X:X="aL",'Batting Standard'!J:J),4)
C9	C9	=LARGE(IF('Batting Standard'!X:X="aL",'Batting Standard'!J:J),5)
C10	C10	=LARGE(IF('Batting Standard'!X:X="aL",'Batting Standard'!J:J),6)
C11	C11	=LARGE(IF('Batting Standard'!X:X="aL",'Batting Standard'!J:J),7)
C12	C12	=LARGE(IF('Batting Standard'!X:X="aL",'Batting Standard'!J:J),8)
C13	C13	=LARGE(IF('Batting Standard'!X:X="aL",'Batting Standard'!J:J),9)
C14	C14	=LARGE(IF('Batting Standard'!X:X="aL",'Batting Standard'!J:J),10)
C15	C15	=IF(LARGE(IF('Batting Standard'!X:X="aL",'Batting Standard'!J:J),11)<>C$14,"",(LARGE(IF('Batting Standard'!X:X="AL",'Batting Standard'!J:J),11)))
C16	C16	=IF(LARGE(IF('Batting Standard'!X:X="aL",'Batting Standard'!J:J),12)<>C$14,"",(LARGE(IF('Batting Standard'!X:X="AL",'Batting Standard'!J:J),12)))
C17	C17	=IF(LARGE(IF('Batting Standard'!X:X="aL",'Batting Standard'!J:J),13)<>C$14,"",(LARGE(IF('Batting Standard'!X:X="AL",'Batting Standard'!J:J),13)))
C18	C18	=IF(LARGE(IF('Batting Standard'!X:X="aL",'Batting Standard'!J:J),14)<>C$14,"",(LARGE(IF('Batting Standard'!X:X="AL",'Batting Standard'!J:J),14)))
C19	C19	=IF(LARGE(IF('Batting Standard'!X:X="aL",'Batting Standard'!J:J),15)<>C$14,"",(LARGE(IF('Batting Standard'!X:X="AL",'Batting Standard'!J:J),15)))
B25:B39	B25	=XLOOKUP(C25&"NL",'Batting Standard'!J:J&'Batting Standard'!X:X,'Batting Standard'!Z:Z,"")
C25	C25	=LARGE(IF('Batting Standard'!X:X="NL",'Batting Standard'!J:J),1)
C26	C26	=LARGE(IF('Batting Standard'!X:X="NL",'Batting Standard'!J:J),2)
C27	C27	=LARGE(IF('Batting Standard'!X:X="NL",'Batting Standard'!J:J),3)
C28	C28	=LARGE(IF('Batting Standard'!X:X="NL",'Batting Standard'!J:J),4)
C29	C29	=LARGE(IF('Batting Standard'!X:X="NL",'Batting Standard'!J:J),5)
C30	C30	=LARGE(IF('Batting Standard'!X:X="NL",'Batting Standard'!J:J),6)
C31	C31	=LARGE(IF('Batting Standard'!X:X="NL",'Batting Standard'!J:J),7)
C32	C32	=LARGE(IF('Batting Standard'!X:X="NL",'Batting Standard'!J:J),8)
C33	C33	=LARGE(IF('Batting Standard'!X:X="NL",'Batting Standard'!J:J),9)
C34	C34	=LARGE(IF('Batting Standard'!X:X="NL",'Batting Standard'!J:J),10)
C35	C35	=IF(LARGE(IF('Batting Standard'!X:X="NL",'Batting Standard'!J:J),11)<>C$14,"",(LARGE(IF('Batting Standard'!X:X="NL",'Batting Standard'!J:J),11)))
C36	C36	=IF(LARGE(IF('Batting Standard'!X:X="NL",'Batting Standard'!J:J),12)<>C$14,"",(LARGE(IF('Batting Standard'!X:X="NL",'Batting Standard'!J:J),12)))
C37:C39	C37	=IF(LARGE(IF('Batting Standard'!X:X="NL",'Batting Standard'!J:J),13)<>C$14,"",(LARGE(IF('Batting Standard'!X:X="NL",'Batting Standard'!J:J),13)))

Tab 2:

Excceljet HR Examples.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	Name	Team	G	AB	PA	H	1B	2B	3B	HR	R	RBI	BB	IBB	SO	HBP	SF	SH	GDP	SB	CS	AVG	AVG2	League	Qual	PlTm
2	Aaron Judge	NYY	139	513	616	159	79	23	0	57	117	123	89	16	156	5	5	0	14	16	3	.310	.310	AL	Yes	Aaron Judge NYY
3	Kyle Schwarber	PHI	137	514	590	110	50	20	2	38	86	80	71	3	181	3	2	0	10	7	1	.214	.214	AL	Yes	Kyle Schwarber PHI
4	Austin Riley	ATL	141	550	616	153	79	36	2	36	82	90	48	1	149	14	4	0	12	2	0	.278	.278	NL	Yes	Austin Riley ATL
5	Paul Goldschmidt	STL	136	509	593	165	93	37	0	35	101	110	75	1	122	4	3	0	6	6	0	.324	.324	NL	Yes	Paul Goldschmidt STL
6	Mike Trout	LAA	102	372	424	104	47	20	2	35	72	70	46	6	120	6	0	0	4	1	0	.280	.280	AL		Mike Trout LAA
7	Pete Alonso	NYM	144	540	616	146	87	24	0	35	79	112	57	15	119	11	8	0	16	3	1	.270	.270	NL	Yes	Pete Alonso NYM
8	Mookie Betts	LAD	123	502	564	138	67	34	3	34	110	77	51	0	89	7	4	0	8	12	1	.275	.275	NL	Yes	Mookie Betts LAD
9	Shohei Ohtani	LAA	138	513	589	136	72	24	6	34	80	88	69	13	143	4	3	0	5	11	9	.265	.265	AL	Yes	Shohei Ohtani LAA
10	Yordan Alvarez	HOU	119	414	498	123	66	22	2	33	85	85	72	9	94	6	6	0	12	1	1	.297	.297	AL	Yes	Yordan Alvarez HOU
11	Christian Walker	ARI	141	506	585	118	64	21	1	32	74	80	66	2	110	7	6	0	14	1	2	.233	.233	NL	Yes	Christian Walker ARI
12	Eugenio Suarez	SEA	141	509	591	120	64	23	2	31	71	84	70	0	182	8	4	0	10	0	0	.236	.236	AL	Yes	Eugenio Suarez SEA
13	Corey Seager	TEX	136	534	601	130	80	19	1	30	81	76	56	7	92	6	5	0	12	3	0	.243	.243	AL	Yes	Corey Seager TEX
14	Rowdy Tellez	MIL	135	469	531	107	54	23	0	30	56	80	56	9	108	5	1	0	18	2	1	.228	.228	NL	Yes	Rowdy Tellez MIL
15	Anthony Rizzo	NYY	117	414	489	93	43	19	1	30	71	71	55	6	93	18	2	0	10	6	3	.225	.225	AL	Yes	Anthony Rizzo NYY
16	Nolan Arenado	STL	133	507	562	151	82	39	1	29	69	96	45	3	68	6	4	0	15	5	3	.298	.298	NL	Yes	Nolan Arenado STL
17	Manny Machado	SDP	133	512	571	152	89	34	1	28	87	90	56	10	116	1	2	0	11	7	1	.297	.297	NL	Yes	Manny Machado SDP
18	Vladimir Guerrero Jr.	TOR	143	569	629	158	97	33	0	28	81	84	51	6	101	6	3	0	24	8	3	.278	.278	AL	Yes	Vladimir Guerrero Jr. TOR
19	C.J. Cron	COL	136	526	576	139	82	26	3	28	74	97	38	6	151	7	4	0	14	0	0	.264	.264	NL	Yes	C.J. Cron COL
20	Rhys Hoskins	PHI	140	524	600	131	74	27	2	28	75	71	66	0	144	6	3	0	10	2	1	.250	.250	NL	Yes	Rhys Hoskins PHI
21	Matt Olson	ATL	143	549	624	131	62	41	0	28	75	91	68	6	146	4	3	0	12	0	0	.239	.239	NL	Yes	Matt Olson ATL
22	Willy Adames	MIL	121	486	535	114	60	26	0	28	75	85	44	2	142	1	4	0	11	5	2	.235	.235	NL	Yes	Willy Adames MIL
23	Byron Buxton	MIN	92	340	382	76	32	13	3	28	61	51	34	0	116	7	1	0	0	6	0	.224	.224	AL		Byron Buxton MIN
Batting Standard

Cell Formulas
Range	Formula
Y2:Y23	Y2	=IF([@PA]>=(XLOOKUP([@Team],Table_4__18[Team],Table_4__18[Qual PA],MAX(Table_4__18[Qual PA]))),"Yes","")
Z2:Z23	Z2	=[@Name]&" "&[@Team]
X2:X19,X21:X23	X2	=XLOOKUP([@Team],Table_4__18[Team],Table_4__18[League],"")
W2:W23	W2	=[@H]/[@AB]

 

[Fluff]

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’)

 

[chasfh]

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’)

Done.

Can you help?

 

[Fluff]

Thanks for that, do you have the CHOOSECOLS function yet?

 

[chasfh]

Thanks for that, do you have the CHOOSECOLS function yet?

I do ...

 

[Fluff]

Ok, that makes things easier, how about

Fluff.xlsm
	A	B	C
1
2
3		HOME RUNS
4		Player	HR
5		Aaron Judge NYY	57
6		Kyle Schwarber PHI	38
7		Mike Trout LAA	35
8		Shohei Ohtani LAA	34
9		Yordan Alvarez HOU	33
10		Eugenio Suarez SEA	31
11		Corey Seager TEX	30
12		Anthony Rizzo NYY	30
13		Vladimir Guerrero Jr. TOR	28
14		Byron Buxton MIN	28
15
16
17
18
19
20
21
22
23		HOME RUNS
24		Player	HR
25		Austin Riley ATL	36
26		Paul Goldschmidt STL	35
27		Pete Alonso NYM	35
28		Mookie Betts LAD	34
29		Christian Walker ARI	32
30		Rowdy Tellez MIL	30
31		Nolan Arenado STL	29
32		Manny Machado SDP	28
33		C.J. Cron COL	28
34		Rhys Hoskins PHI	28
35		Matt Olson ATL	28
36		Willy Adames MIL	28
37
Main

Cell Formulas
Range	Formula
B5:C14	B5	=SORT(FILTER(CHOOSECOLS(Table2[[HR]:[PlTm]],-1,1),(Table2[League]="AL")*(Table2[HR]>=LARGE(IF(Table2[League]="AL",Table2[HR]),10))),2,-1)
B25:C36	B25	=SORT(FILTER(CHOOSECOLS(Table2[[HR]:[PlTm]],-1,1),(Table2[League]="NL")*(Table2[HR]>=LARGE(IF(Table2[League]="NL",Table2[HR]),10))),2,-1)
Dynamic array formulas.

You will need to change Table2 to the name of your Table.

 

[chasfh]

Ok, that makes things easier, how about

Fluff.xlsm
	A	B	C
1
2
3		HOME RUNS
4		Player	HR
5		Aaron Judge NYY	57
6		Kyle Schwarber PHI	38
7		Mike Trout LAA	35
8		Shohei Ohtani LAA	34
9		Yordan Alvarez HOU	33
10		Eugenio Suarez SEA	31
11		Corey Seager TEX	30
12		Anthony Rizzo NYY	30
13		Vladimir Guerrero Jr. TOR	28
14		Byron Buxton MIN	28
15
16
17
18
19
20
21
22
23		HOME RUNS
24		Player	HR
25		Austin Riley ATL	36
26		Paul Goldschmidt STL	35
27		Pete Alonso NYM	35
28		Mookie Betts LAD	34
29		Christian Walker ARI	32
30		Rowdy Tellez MIL	30
31		Nolan Arenado STL	29
32		Manny Machado SDP	28
33		C.J. Cron COL	28
34		Rhys Hoskins PHI	28
35		Matt Olson ATL	28
36		Willy Adames MIL	28
37
Main

Cell Formulas
Range	Formula
B5:C14	B5	=SORT(FILTER(CHOOSECOLS(Table2[[HR]:[PlTm]],-1,1),(Table2[League]="AL")*(Table2[HR]>=LARGE(IF(Table2[League]="AL",Table2[HR]),10))),2,-1)
B25:C36	B25	=SORT(FILTER(CHOOSECOLS(Table2[[HR]:[PlTm]],-1,1),(Table2[League]="NL")*(Table2[HR]>=LARGE(IF(Table2[League]="NL",Table2[HR]),10))),2,-1)
Dynamic array formulas.

You will need to change Table2 to the name of your Table.

No dice. I changed the name of Table2 to the referring tab and it barfed, flagging the part you refer to the

---

column the first time:

 

[chasfh]

Ok, that makes things easier, how about

Fluff.xlsm
	A	B	C
1
2
3		HOME RUNS
4		Player	HR
5		Aaron Judge NYY	57
6		Kyle Schwarber PHI	38
7		Mike Trout LAA	35
8		Shohei Ohtani LAA	34
9		Yordan Alvarez HOU	33
10		Eugenio Suarez SEA	31
11		Corey Seager TEX	30
12		Anthony Rizzo NYY	30
13		Vladimir Guerrero Jr. TOR	28
14		Byron Buxton MIN	28
15
16
17
18
19
20
21
22
23		HOME RUNS
24		Player	HR
25		Austin Riley ATL	36
26		Paul Goldschmidt STL	35
27		Pete Alonso NYM	35
28		Mookie Betts LAD	34
29		Christian Walker ARI	32
30		Rowdy Tellez MIL	30
31		Nolan Arenado STL	29
32		Manny Machado SDP	28
33		C.J. Cron COL	28
34		Rhys Hoskins PHI	28
35		Matt Olson ATL	28
36		Willy Adames MIL	28
37
Main

Cell Formulas
Range	Formula
B5:C14	B5	=SORT(FILTER(CHOOSECOLS(Table2[[HR]:[PlTm]],-1,1),(Table2[League]="AL")*(Table2[HR]>=LARGE(IF(Table2[League]="AL",Table2[HR]),10))),2,-1)
B25:C36	B25	=SORT(FILTER(CHOOSECOLS(Table2[[HR]:[PlTm]],-1,1),(Table2[League]="NL")*(Table2[HR]>=LARGE(IF(Table2[League]="NL",Table2[HR]),10))),2,-1)
Dynamic array formulas.

You will need to change Table2 to the name of your Table.

It occurred to me that the problem may have been you were referring to a table, and I was referring to another entire tab. So I went to the table of the other tab, found it was called Table_4, made that change in the formula, and ...

... barf.

I wonder whether part of the problem might be that the example I sent you was a truncated table that included only the top several players who actually qualified for the table, which is 22 rows of players, when in reality my formula is sifting through a table of 672 rows, not sorted by homers. Might that be part of the issue? Although when I truncated that referred table, I still got the #SPILL error ...

 

[Fluff]

Did you put the formula in H5 only?

 

[chasfh]

Yes, I did try it in only H5, same #SPILL! problem: