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]

You are getting the spill error as there are other populated cells in the way of the formula. You need to clear them

 

[chasfh]

I'm not quite sure what you mean—can you show me what that would look like, using my original post as an example? Or at least tell me which exact cells I would need to clear out? I appreciate your patience.

 

[Fluff]

can you show me what that would look like, using my original post as an example?

I already have, it's in post#6.

The image in post#10 shows which cells need to be cleared, it's all the ones inside the blue border.

 

[chasfh]

I already have, it's in post#6.

The image in post#10 shows which cells need to be cleared, it's all the ones inside the blue border.

Yes, that's it! I did not realize that the #SPILL! error pertained to the results table and not the source table. I'm relatively new to intermediate Excel concepts.

Thanks again for your patience.

 

[Fluff]

Glad it's working & thanks for the feedback.