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

chasfh

Board Regular
Joined
Dec 10, 2014
Messages
54
Office Version
  1. 365
Platform
  1. Windows
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:

PlayerHR
Austin Riley ATL36
Paul Goldschmidt STL35
Paul Goldschmidt STL35
Mookie Betts LAD34
Christian Walker ARI32
Rowdy Tellez MIL30
Nolan Arenado STL29
Manny Machado SDP28
Manny Machado SDP28
Manny Machado SDP28
Manny Machado SDP28
Manny Machado SDP28

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:

Cell Formulas
RangeFormula
B5:B19B5=XLOOKUP(C5&"AL",'Batting Standard'!J:J&'Batting Standard'!X:X,'Batting Standard'!Z:Z,"")
C5C5=LARGE(IF('Batting Standard'!X:X="aL",'Batting Standard'!J:J),1)
C6C6=LARGE(IF('Batting Standard'!X:X="aL",'Batting Standard'!J:J),2)
C7C7=LARGE(IF('Batting Standard'!X:X="aL",'Batting Standard'!J:J),3)
C8C8=LARGE(IF('Batting Standard'!X:X="aL",'Batting Standard'!J:J),4)
C9C9=LARGE(IF('Batting Standard'!X:X="aL",'Batting Standard'!J:J),5)
C10C10=LARGE(IF('Batting Standard'!X:X="aL",'Batting Standard'!J:J),6)
C11C11=LARGE(IF('Batting Standard'!X:X="aL",'Batting Standard'!J:J),7)
C12C12=LARGE(IF('Batting Standard'!X:X="aL",'Batting Standard'!J:J),8)
C13C13=LARGE(IF('Batting Standard'!X:X="aL",'Batting Standard'!J:J),9)
C14C14=LARGE(IF('Batting Standard'!X:X="aL",'Batting Standard'!J:J),10)
C15C15=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)))
C16C16=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)))
C17C17=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)))
C18C18=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)))
C19C19=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:B39B25=XLOOKUP(C25&"NL",'Batting Standard'!J:J&'Batting Standard'!X:X,'Batting Standard'!Z:Z,"")
C25C25=LARGE(IF('Batting Standard'!X:X="NL",'Batting Standard'!J:J),1)
C26C26=LARGE(IF('Batting Standard'!X:X="NL",'Batting Standard'!J:J),2)
C27C27=LARGE(IF('Batting Standard'!X:X="NL",'Batting Standard'!J:J),3)
C28C28=LARGE(IF('Batting Standard'!X:X="NL",'Batting Standard'!J:J),4)
C29C29=LARGE(IF('Batting Standard'!X:X="NL",'Batting Standard'!J:J),5)
C30C30=LARGE(IF('Batting Standard'!X:X="NL",'Batting Standard'!J:J),6)
C31C31=LARGE(IF('Batting Standard'!X:X="NL",'Batting Standard'!J:J),7)
C32C32=LARGE(IF('Batting Standard'!X:X="NL",'Batting Standard'!J:J),8)
C33C33=LARGE(IF('Batting Standard'!X:X="NL",'Batting Standard'!J:J),9)
C34C34=LARGE(IF('Batting Standard'!X:X="NL",'Batting Standard'!J:J),10)
C35C35=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)))
C36C36=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:C39C37=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
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1NameTeamGABPAH1B2B3BHRRRBIBBIBBSOHBPSFSHGDPSBCSAVGAVG2LeagueQualPlTm
2Aaron JudgeNYY1395136161597923057117123891615655014163.310.310ALYesAaron Judge NYY
3Kyle SchwarberPHI137514590110502023886807131813201071.214.214ALYesKyle Schwarber PHI
4Austin RileyATL1415506161537936236829048114914401220.278.278NLYesAustin Riley ATL
5Paul GoldschmidtSTL1365095931659337035101110751122430660.324.324NLYesPaul Goldschmidt STL
6Mike TroutLAA10237242410447202357270466120600410.280.280AL Mike Trout LAA
7Pete AlonsoNYM144540616146872403579112571511911801631.270.270NLYesPete Alonso NYM
8Mookie BettsLAD123502564138673433411077510897408121.275.275NLYesMookie Betts LAD
9Shohei OhtaniLAA1385135891367224634808869131434305119.265.265ALYesShohei Ohtani LAA
10Yordan AlvarezHOU11941449812366222338585729946601211.297.297ALYesYordan Alvarez HOU
11Christian WalkerARI141506585118642113274806621107601412.233.233NLYesChristian Walker ARI
12Eugenio SuarezSEA141509591120642323171847001828401000.236.236ALYesEugenio Suarez SEA
13Corey SeagerTEX13653460113080191308176567926501230.243.243ALYesCorey Seager TEX
14Rowdy TellezMIL135469531107542303056805691085101821.228.228NLYesRowdy Tellez MIL
15Anthony RizzoNYY11741448993431913071715569318201063.225.225ALYesAnthony Rizzo NYY
16Nolan ArenadoSTL13350756215182391296996453686401553.298.298NLYesNolan Arenado STL
17Manny MachadoSDP1335125711528934128879056101161201171.297.297NLYesManny Machado SDP
18Vladimir Guerrero Jr.TOR143569629158973302881845161016302483.278.278ALYesVladimir Guerrero Jr. TOR
19C.J. CronCOL136526576139822632874973861517401400.264.264NLYesC.J. Cron COL
20Rhys HoskinsPHI140524600131742722875716601446301021.250.250NLYesRhys Hoskins PHI
21Matt OlsonATL143549624131624102875916861464301200.239.239NLYesMatt Olson ATL
22Willy AdamesMIL121486535114602602875854421421401152.235.235NLYesWilly Adames MIL
23Byron BuxtonMIN923403827632133286151340116710060.224.224AL Byron Buxton MIN
Batting Standard
Cell Formulas
RangeFormula
Y2:Y23Y2=IF([@PA]>=(XLOOKUP([@Team],Table_4__18[Team],Table_4__18[Qual PA],MAX(Table_4__18[Qual PA]))),"Yes","")
Z2:Z23Z2=[@Name]&" "&[@Team]
X2:X19,X21:X23X2=XLOOKUP([@Team],Table_4__18[Team],Table_4__18[League],"")
W2:W23W2=[@H]/[@AB]
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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’)
 
Upvote 0
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?
 
Upvote 0
Thanks for that, do you have the CHOOSECOLS function yet?
 
Upvote 0
Ok, that makes things easier, how about
Fluff.xlsm
ABC
1
2
3HOME RUNS
4PlayerHR
5Aaron Judge NYY57
6Kyle Schwarber PHI38
7Mike Trout LAA35
8Shohei Ohtani LAA34
9Yordan Alvarez HOU33
10Eugenio Suarez SEA31
11Corey Seager TEX30
12Anthony Rizzo NYY30
13Vladimir Guerrero Jr. TOR28
14Byron Buxton MIN28
15
16
17
18
19
20
21
22
23HOME RUNS
24PlayerHR
25Austin Riley ATL36
26Paul Goldschmidt STL35
27Pete Alonso NYM35
28Mookie Betts LAD34
29Christian Walker ARI32
30Rowdy Tellez MIL30
31Nolan Arenado STL29
32Manny Machado SDP28
33C.J. Cron COL28
34Rhys Hoskins PHI28
35Matt Olson ATL28
36Willy Adames MIL28
37
Main
Cell Formulas
RangeFormula
B5:C14B5=SORT(FILTER(CHOOSECOLS(Table2[[HR]:[PlTm]],-1,1),(Table2[League]="AL")*(Table2[HR]>=LARGE(IF(Table2[League]="AL",Table2[HR]),10))),2,-1)
B25:C36B25=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.
 
Upvote 0
Solution
Ok, that makes things easier, how about
Fluff.xlsm
ABC
1
2
3HOME RUNS
4PlayerHR
5Aaron Judge NYY57
6Kyle Schwarber PHI38
7Mike Trout LAA35
8Shohei Ohtani LAA34
9Yordan Alvarez HOU33
10Eugenio Suarez SEA31
11Corey Seager TEX30
12Anthony Rizzo NYY30
13Vladimir Guerrero Jr. TOR28
14Byron Buxton MIN28
15
16
17
18
19
20
21
22
23HOME RUNS
24PlayerHR
25Austin Riley ATL36
26Paul Goldschmidt STL35
27Pete Alonso NYM35
28Mookie Betts LAD34
29Christian Walker ARI32
30Rowdy Tellez MIL30
31Nolan Arenado STL29
32Manny Machado SDP28
33C.J. Cron COL28
34Rhys Hoskins PHI28
35Matt Olson ATL28
36Willy Adames MIL28
37
Main
Cell Formulas
RangeFormula
B5:C14B5=SORT(FILTER(CHOOSECOLS(Table2[[HR]:[PlTm]],-1,1),(Table2[League]="AL")*(Table2[HR]>=LARGE(IF(Table2[League]="AL",Table2[HR]),10))),2,-1)
B25:C36B25=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:

1663363972965.png


1663363993977.png
 
Upvote 0
Ok, that makes things easier, how about
Fluff.xlsm
ABC
1
2
3HOME RUNS
4PlayerHR
5Aaron Judge NYY57
6Kyle Schwarber PHI38
7Mike Trout LAA35
8Shohei Ohtani LAA34
9Yordan Alvarez HOU33
10Eugenio Suarez SEA31
11Corey Seager TEX30
12Anthony Rizzo NYY30
13Vladimir Guerrero Jr. TOR28
14Byron Buxton MIN28
15
16
17
18
19
20
21
22
23HOME RUNS
24PlayerHR
25Austin Riley ATL36
26Paul Goldschmidt STL35
27Pete Alonso NYM35
28Mookie Betts LAD34
29Christian Walker ARI32
30Rowdy Tellez MIL30
31Nolan Arenado STL29
32Manny Machado SDP28
33C.J. Cron COL28
34Rhys Hoskins PHI28
35Matt Olson ATL28
36Willy Adames MIL28
37
Main
Cell Formulas
RangeFormula
B5:C14B5=SORT(FILTER(CHOOSECOLS(Table2[[HR]:[PlTm]],-1,1),(Table2[League]="AL")*(Table2[HR]>=LARGE(IF(Table2[League]="AL",Table2[HR]),10))),2,-1)
B25:C36B25=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 ...

1663364499315.png


... 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 ...
 
Upvote 0
Did you put the formula in H5 only?
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top