Formula to determine RANK.EQ array based on varying total # of cells

[Preacherman771]

In the attached screenshot,
Y48:Y55 is the grouping of duplicates from another table
Z48:Z55 is the corresponding names of each duplicate from the other table
AA48:AA55 is the total duplicates of each group from the other table
AB48:AB55 is the conditional calculated results for this table
AC48:AC55 Ranking of AB based on number in column AA
AD48:AD55 Index Z based on ranking
AE48:AE55 Index AB based on ranking
In the total project, the data in each table/worksheet, including the current one, changes based on the data entered on the primary table on a linked worksheet. What I am looking for is a way to write the RANK.EQ formula in AC48:AC45 based on AA48:AA55 as the quantities change in the future. For example, with the current numbers, based on AA48 (3) the 1st formula would be, RANK.EQ(AB48,$AB$48:$AB$50,0)+COUNTIF(AB$48:AB48,AB48)-1. AA49 (3) RANK.EQ(AB51,$AB$51:$AB$53,0)+. . . ), AA50 (2), RANK.EQ(AB54,$AB$54:$AB$55,0)+. . . ). But if AA48 changes to let's say 2, the formula would also change to RANK.EQ(AB48,$AB$48:$AB$49,0)+ . . . ), etc. As the numbers in column AA change, is there a way to write the formula which would also adjust based on the numbers in column AA?

NFL 2021-2022 Standings (Template).xlsx
	Y	Z	AA	AB	AC	AD	AE	AF
47	Group #1
48	a	PIT	3	0.667			1
49	a	BAL	3	0.667			2
50	a	CLE	2	0.625			3
51	b	IND		0.500			4
52	b	TEN		0.667			5
53	b	MIA		0.750			6
54	c	NE		0.000			7
55	c	KC		0.000			8
Calc_Ties (Conf)

Cell Formulas
Range	Formula
Y47	Y47	="Group #"&$K$7
AA48:AA55	AA48	=IF(COUNTIF($Y$48:$Y$55,J7)<>0,COUNTIF($Y$48:$Y$55,J7),"")
AB48:AB55	AB48	=IFERROR(INDEX($DC$36:$DC$106,MATCH(AH28,$CL$36:$CL$106,0)),"")
Z48:Z53	Z48	=IFERROR(INDEX($L$7:$L$22,SMALL(IF($P$7:$P$22=1,ROW($P$7:$P$22)-ROW($P$7)+1),K7)),"")

 

[StephenCrump]

Your formulae could be simplified if you used 365's dynamic function capability. I'm guessing you need backwards compatibility?

Try replacing: AC48: =RANK.EQ(AB48,$AB$48:$AB$50,0)

with: =RANK.EQ(INDEX(AB$48:AB$55,SUM(AA$48:AA48)-AA48+1),INDEX(AB$48:AB$55,SUM(AA$48:AA48)-AA48+1):INDEX(AB$48:AB$55,SUM(AA$48:AA48)),0)

 

[Preacherman771]

Thanks for the response and suggestion. I copied and pasted your suggestion unfortunately it didn't work. How would I go about simplifying my formula since I do have Office 365? What dynamic function are you thinking of?

 

[StephenCrump]

How would I go about simplifying my formula since I do have Office 365? What dynamic function are you thinking of?

Some of your formulae don't immediately make sense to me, but that may be because you've posted only part of your worksheet.

I am guessing K7 is 1, K8 is 2 etc, in which case, you could start by replacing
Z48:Z53: =IFERROR(INDEX($L$7:$L$22,SMALL(IF($P$7:$P$22=1,ROW($P$7:$P$22)-ROW($P$7)+1),K7)),"")
with the simple: =FILTER($L$7:$L$22,$P$7:$P$22=1)

It's not clear why you've hard-coded Z54:Z55?

Thanks for the response and suggestion. I copied and pasted your suggestion unfortunately it didn't work.

Here's just the RANK.EQ part of the formula - I can replicate your results using just the one formula:

	Y	Z	AA	AB	AC	AD
47					You	Me
48	a	PIT	3	0.600	2	2
49	a	BAL	3	0.700	1	1
50	a	CLE	2	0.100	2	2
51	b	IND		0.900
52	b	TEN		0.667
53	b	MIA		0.750
54	c	NE		0.000
55	c	KC		0.200
Sheet3

Cell Formulas
Range	Formula
AC48	AC48	=RANK.EQ(AB48,$AB$48:$AB$50,0)
AD48:AD50	AD48	=RANK.EQ(INDEX(AB$48:AB$55,SUM(AA$48:AA48)-AA48+1),INDEX(AB$48:AB$55,SUM(AA$48:AA48)-AA48+1):INDEX(AB$48:AB$55,SUM(AA$48:AA48)),0)
AC49	AC49	=RANK.EQ(AB51,$AB$51:$AB$53,0)
AC50	AC50	=RANK.EQ(AB54,$AB$54:$AB$55,0)
AA51:AA55	AA51	=IF(COUNTIF($Y$48:$Y$55,J10)<>0,COUNTIF($Y$48:$Y$55,J10),"")

 

[Preacherman771]

You are correct in that this is only part of the worksheet and K7,K8, etc. is 1,2, etc. Z54 & Z55 are hard entered to finish filling range for illustration purpose. Let me see if I can help clarify my project some.

I am creating an interactive football spreadsheet that calculates standings, statistics, standings, etc, based on the data entered by an individual. The current worksheet starts with the AFC field of 16 teams ranked with W-L-T percentage, and once this 16 field is set up will set up one for the NFC 16 team field also. If there are any duplicate percentages, whether 1 set or multiple, a table assigns these set(s) of similar duplicates into groups for each set. With the ultimate goal of ranking each team from 1 to 16, using the NFL tie breaking rules, conditional tables for each rule/step individually take each group(s) with any remaining duplicate teams till each group(s) have no duplicates. This grouping explains the presence of Group 1, Group 2, etc., which I now realize does not show on the post. The post is the computation of Group 1 to be replicated for each other group as needed. As each group proceeds through the process of each tie breaking step within each group, e.g. best division games pct, it will eliminate some or all starting duplicate teams based on the new pct rule. But it may also result in a single duplicate percentage or 2 or more different ones. At the beginning of the next tie breaking step, any similar duplicate(s) are assigned as a, b, etc. thus Column Y, and in Column Z listing each team using Index & Match. When writing the formula to rank the results of the new tie breaking step, it needs to rank all of the a's, b's, etc. separately in Group 1. Column AA is a helper field calculating the number of a's, b's, etc. if any. Since it is interactive, will follow future football seasons, the numbers and information will change. Therefore, all formulas need to compute automatically based on these changes. Thus far I have been able to create necessary tables with formulas. However, I am stumped on this one needing to not only rank the fields pertaining to the current pairing of different duplicates but to do so that it will automatically do so as new data is inputted on the input table.

 

[StephenCrump]

It would be great if you could distil some data down to something that can be posted completely using XL2BB?

And, for the NFL non-believers, explain any relevant tiebreaker rules you're using.

 

[Preacherman771]

I have posted the first two rounds of tie breaking rules, which are 1) Best Head to Head Percentage (each team's total w-l-t percentage from head to head with each of the other teams), 2) Best Division Percentage (each teams total w-l-t percentage of division only games). Each Round's total percentage's are retrieved from another calculation table. Each following Round would follow the same basic formulas as well as Group 2- Group 8.

Table with Columns O thru W is the starting table where each of the following calculations are based off.
Column Y is the delineation of duplicate teams from previous round.
Column Z how many similar total duplicates from previous round.
Column AA Indexed teams Matched to assigned group #.
Column AB Ranking of Rounds result calculation.
Column AC Calculation of Rounds result.
Column AE Listing rounds calculation result best to worst.
Column AF Assigning letter value to similar duplicate teams.
Column AG Indexed teams Matched to assigned letter value.

I greatly appreciate your help and interest. Thank you.

NFL 2021-2022 Standings (Template).xlsx
	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
26		Team		All			Div		Conf		ROUND 1			HEAD TO HEAD GAMES
27	1	KC	*	0.900	8	1	0.667		0.833		Group #1
28	2	BUF	*	0.900	8	1	1.000		0.833		*	8	KC	3	0.725	1	0.975	a	CLE
29	3	PIT	*	0.900		1	0.667		0.750		*		BUF	7	0.550	2	0.975	a	IND
30	4	BAL	*	0.900		1	0.667		0.583		*		PIT	5	0.600	3	0.725	b	KC
31	5	CLE	*	0.900		1	0.500		0.583		*		BAL	4	0.725	4	0.725	b	BAL
32	6	IND	*	0.900		1	0.667		0.583		*		CLE	1	0.975	5	0.600	c	PIT
33	7	TEN	*	0.900		1	0.833		0.667		*		IND	2	0.975	6	0.600	c	TEN
34	8	MIA	*	0.900		1	0.500		0.583		*		TEN	6	0.600	7	0.550	d	BUF
35	9	LV	*	0.800		2	0.667		0.500		*		MIA	8	0.550	8	0.550	d	MIA
36	10	NE	*	0.800		2	0.500		0.500		ROUND 2			DIVISION GAMES
37	11	LAC	*	0.800		2	0.500		0.500		Group #1
38	12	DEN	*	0.800		2	0.167		0.333		a	2	CLE		0.500	1
39	13	CIN	*	0.800		2	0.167		0.333		a	2	IND		0.667	2
40	14	HOU	*	0.800		2	0.333		0.250		b	2	KC		0.667	3
41	15	NYJ	*	0.800		2	0.000		0.083		b	2	BAL		0.667	4
42	16	JAX	*	0.800		2	0.167		0.083		c		PIT		0.667	5
43											c		TEN		0.833	6
44											d		BUF		1.000	7
45											d		MIA		0.500	8
Calc_Ties (Conf)

Cell Formulas
Range	Formula
Y26	Y26	="ROUND "&$O27
P27:P42	P27	=IFERROR(INDEX(Tab_TeamStat[Abr],MATCH(1,(O27=Tab_TeamStat[ConfRnk])*($O$25=Tab_TeamStat[Conf]),0)),"---")
Q27:Q41	Q27	=IF(AND(R27=R28,OR(ROWS(R$28:R28)=1,ROWS(R$28:R28)=16)),"*",IF(OR(R27=R26,R27=R28),"*",""))
R27:R42	R27	=IF(I7<>"",I7,"")
S27:S42	S27	=IF(COUNTIF(T$27:T$42,O27)=0,"",COUNTIF(T$27:T$42,O27))
T27:T42	T27	=IF(Q27<>"*","", IF(ROWS(T$26:T26)=COUNTIF(T$26:T26,""),1, IF(AND(T26=1,R27<>R26),2, IF(T26=1,1, IF(ROWS(T$26:T26)-COUNTIF(T$26:T26,"")=COUNTIF(T$26:T26,1),2, IF(AND(T26=2,R27<>R26),3, IF(T26=2,2, IF(ROWS(T$26:T26)-COUNTIF(T$26:T26,"")=COUNTIF(T$26:T26,2),3, IF(AND(T26=3,R27<>R26),4, IF(T26=3,3, IF(ROWS(T$26:T26)-COUNTIF(T$26:T26,"")=COUNTIF(T$26:T26,3),4, IF(AND(T26=4,R27<>R26),5, IF(T26=4,4, IF(ROWS(T$26:T26)-COUNTIF(T$26:T26,"")=COUNTIF(T$26:T26,4),5, IF(AND(T26=5,R27<>R26),6, IF(T26=5,5, IF(ROWS(T$26:T26)-COUNTIF(T$26:T26,"")=COUNTIF(T$26:T26,5),6, IF(AND(T26=6,R27<>R26),7, IF(T26=6,6, IF(ROWS(T$26:T26)-COUNTIF(T$26:T26,"")=COUNTIF(T$26:T26,6),7, IF(AND(T26=7,R27<>R26),8, IF(T26=7,7, IF(ROWS(T$26:T26)-COUNTIF(T$26:T26,"")=COUNTIF(T$26:T26,7),8, IF(AND(T26=8,R27<>R26),8, IF(T26=8,8,"Z")))))))))))))))))))))))))
U27:U42	U27	=IFERROR(INDEX(Tab_TeamStat[DivPct],MATCH($P27,Tab_TeamStat[Abr],0)),"---")
Q42	Q42	=IF(AND(R42=N23,OR(ROWS(N$8:N23)=1,ROWS(N$8:N23)=16)),"*",IF(OR(R42=R41,R42=N23),"*",""))
Y27,Y37	Y27	="Group #"&$O$27
Z28	Z28	=SUM(8-COUNTIF(Y$28:Y$35,""))
AA28:AA35	AA28	=IFERROR(INDEX($P$27:$P$42,SMALL(IF($T$27:$T$42=1,ROW($T$27:$T$42)-ROW($T$27)+1),$O27)),"")
AB28:AB35	AB28	=IFERROR(RANK.EQ(AC28,AC$28:AC$35,0)+COUNTIF(AC$28:AC28,AC28)-1,"")
AC28:AC35	AC28	=AC6
AD28	AD28	=IF(Y28="","",IF(ROWS(AD$28:AD28)=1,1,IF(AE27="",1,AE27+1)))
AE28:AE35	AE28	=IF(AA28<>"",INDEX(AC$28:AC$35,MATCH(AD28,AB$28:AB$35,0)),"")
AF28,AF38:AF45	AF28	=IF(AE28="","", IF(AND(COUNTIF(AG$27:AG27,"")=ROWS(AF$28:AF28)-1,AE28=AE29),"a", IF(AND(AE28<>AF27,AE28<>AE29),"", IF(AG27="", (IF(COUNTIF(AG$27:AG27,"a")=0,"a", IF(COUNTIF(AG$27:AG27,"b")=0,"b", IF(COUNTIF(AG$27:AG27,"c")=0,"c","d")))), IF(AG27="a", (IF(AND(AE28<>AF27,AE28=AE29),"b", IF(OR(AE28=AF27,AE28=AE29),"a", IF(AND(AE28=AF27,AE28<>AE29),"a","")))), IF(AG27="b", (IF(AND(AE28<>AF27,AE28=AE29),"c", IF(OR(AE28=AF27,AE28=AE29),"b", IF(AND(AE28=AF27,AE28<>AE29),"b","c")))), IF(AG27="c", (IF(AND(AE28<>AF27,AE28=AE29),"d", IF(OR(AE28=AF27,AE28=AE29),"c", IF(AND(AE28=AF27,AE28<>AE29),"c","d")))), IF(AG27="d", (IF(AND(AE28<>AF27,AE28=AE29),"e", IF(OR(AE28=AF27,AE28=AE29),"d", IF(AND(AE28=AF27,AE28<>AE29),"d","e")))),"Z"))))))))
AG28:AG35	AG28	=IF(AE28<>"",INDEX(AA$28:AA$35,MATCH(AD28,AB$28:AB$35,0)),"")
AD29:AD35	AD29	=IF(Y29="","",IF(ROWS(AD$28:AD29)=1,1,IF(AD28="",1,AD28+1)))
AF29	AF29	=IF(AE29="","", IF(AND(COUNTIF(AG$27:AG27,"")=ROWS(AF$28:AF29)-1,AE29=AE30),"a", IF(AND(AE29<>AE28,AE29<>AE30),"", IF(AF28="", (IF(COUNTIF(AG$27:AG27,"a")=0,"a", IF(COUNTIF(AG$27:AG27,"b")=0,"b", IF(COUNTIF(AG$27:AG27,"c")=0,"c","d")))), IF(AF28="a", (IF(AND(AE29<>AE28,AE29=AE30),"b", IF(OR(AE29=AE28,AE29=AE30),"a", IF(AND(AE29=AE28,AE29<>AE30),"a","")))), IF(AF28="b", (IF(AND(AE29<>AE28,AE29=AE30),"c", IF(OR(AE29=AE28,AE29=AE30),"b", IF(AND(AE29=AE28,AE29<>AE30),"b","c")))), IF(AF28="c", (IF(AND(AE29<>AE28,AE29=AE30),"d", IF(OR(AE29=AE28,AE29=AE30),"c", IF(AND(AE29=AE28,AE29<>AE30),"c","d")))), IF(AF28="d", (IF(AND(AE29<>AE28,AE29=AE30),"e", IF(OR(AE29=AE28,AE29=AE30),"d", IF(AND(AE29=AE28,AE29<>AE30),"d","e")))),"Z"))))))))
AF30	AF30	=IF(AE30="","", IF(AND(COUNTIF(AG$27:AG27,"")=ROWS(AF$28:AF30)-1,AE30=AE31),"a", IF(AND(AE30<>AE29,AE30<>AE31),"", IF(AF29="", (IF(COUNTIF(AG$27:AG27,"a")=0,"a", IF(COUNTIF(AG$27:AG27,"b")=0,"b", IF(COUNTIF(AG$27:AG27,"c")=0,"c","d")))), IF(AF29="a", (IF(AND(AE30<>AE29,AE30=AE31),"b", IF(OR(AE30=AE29,AE30=AE31),"a", IF(AND(AE30=AE29,AE30<>AE31),"a","")))), IF(AF29="b", (IF(AND(AE30<>AE29,AE30=AE31),"c", IF(OR(AE30=AE29,AE30=AE31),"b", IF(AND(AE30=AE29,AE30<>AE31),"b","c")))), IF(AF29="c", (IF(AND(AE30<>AE29,AE30=AE31),"d", IF(OR(AE30=AE29,AE30=AE31),"c", IF(AND(AE30=AE29,AE30<>AE31),"c","d")))), IF(AF29="d", (IF(AND(AE30<>AE29,AE30=AE31),"e", IF(OR(AE30=AE29,AE30=AE31),"d", IF(AND(AE30=AE29,AE30<>AE31),"d","e")))),"Z"))))))))
AF31	AF31	=IF(AE31="","", IF(AND(COUNTIF(AG$27:AG27,"")=ROWS(AF$28:AF31)-1,AE31=AE32),"a", IF(AND(AE31<>AE30,AE31<>AE32),"", IF(AF30="", (IF(COUNTIF(AG$27:AG27,"a")=0,"a", IF(COUNTIF(AG$27:AG27,"b")=0,"b", IF(COUNTIF(AG$27:AG27,"c")=0,"c","d")))), IF(AF30="a", (IF(AND(AE31<>AE30,AE31=AE32),"b", IF(OR(AE31=AE30,AE31=AE32),"a", IF(AND(AE31=AE30,AE31<>AE32),"a","")))), IF(AF30="b", (IF(AND(AE31<>AE30,AE31=AE32),"c", IF(OR(AE31=AE30,AE31=AE32),"b", IF(AND(AE31=AE30,AE31<>AE32),"b","c")))), IF(AF30="c", (IF(AND(AE31<>AE30,AE31=AE32),"d", IF(OR(AE31=AE30,AE31=AE32),"c", IF(AND(AE31=AE30,AE31<>AE32),"c","d")))), IF(AF30="d", (IF(AND(AE31<>AE30,AE31=AE32),"e", IF(OR(AE31=AE30,AE31=AE32),"d", IF(AND(AE31=AE30,AE31<>AE32),"d","e")))),"Z"))))))))
AF32	AF32	=IF(AE32="","", IF(AND(COUNTIF(AG$27:AG27,"")=ROWS(AF$28:AF32)-1,AE32=AE33),"a", IF(AND(AE32<>AE31,AE32<>AE33),"", IF(AF31="", (IF(COUNTIF(AG$27:AG27,"a")=0,"a", IF(COUNTIF(AG$27:AG27,"b")=0,"b", IF(COUNTIF(AG$27:AG27,"c")=0,"c","d")))), IF(AF31="a", (IF(AND(AE32<>AE31,AE32=AE33),"b", IF(OR(AE32=AE31,AE32=AE33),"a", IF(AND(AE32=AE31,AE32<>AE33),"a","")))), IF(AF31="b", (IF(AND(AE32<>AE31,AE32=AE33),"c", IF(OR(AE32=AE31,AE32=AE33),"b", IF(AND(AE32=AE31,AE32<>AE33),"b","c")))), IF(AF31="c", (IF(AND(AE32<>AE31,AE32=AE33),"d", IF(OR(AE32=AE31,AE32=AE33),"c", IF(AND(AE32=AE31,AE32<>AE33),"c","d")))), IF(AF31="d", (IF(AND(AE32<>AE31,AE32=AE33),"e", IF(OR(AE32=AE31,AE32=AE33),"d", IF(AND(AE32=AE31,AE32<>AE33),"d","e")))),"Z"))))))))
AF33	AF33	=IF(AE33="","", IF(AND(COUNTIF(AG$27:AG27,"")=ROWS(AF$28:AF33)-1,AE33=AE34),"a", IF(AND(AE33<>AE32,AE33<>AE34),"", IF(AF32="", (IF(COUNTIF(AG$27:AG27,"a")=0,"a", IF(COUNTIF(AG$27:AG27,"b")=0,"b", IF(COUNTIF(AG$27:AG27,"c")=0,"c","d")))), IF(AF32="a", (IF(AND(AE33<>AE32,AE33=AE34),"b", IF(OR(AE33=AE32,AE33=AE34),"a", IF(AND(AE33=AE32,AE33<>AE34),"a","")))), IF(AF32="b", (IF(AND(AE33<>AE32,AE33=AE34),"c", IF(OR(AE33=AE32,AE33=AE34),"b", IF(AND(AE33=AE32,AE33<>AE34),"b","c")))), IF(AF32="c", (IF(AND(AE33<>AE32,AE33=AE34),"d", IF(OR(AE33=AE32,AE33=AE34),"c", IF(AND(AE33=AE32,AE33<>AE34),"c","d")))), IF(AF32="d", (IF(AND(AE33<>AE32,AE33=AE34),"e", IF(OR(AE33=AE32,AE33=AE34),"d", IF(AND(AE33=AE32,AE33<>AE34),"d","e")))),"Z"))))))))
AF34	AF34	=IF(AE34="","", IF(AND(COUNTIF(AG$27:AG27,"")=ROWS(AF$28:AF34)-1,AE34=AE35),"a", IF(AND(AE34<>AE33,AE34<>AE35),"", IF(AF33="", (IF(COUNTIF(AG$27:AG27,"a")=0,"a", IF(COUNTIF(AG$27:AG27,"b")=0,"b", IF(COUNTIF(AG$27:AG27,"c")=0,"c","d")))), IF(AF33="a", (IF(AND(AE34<>AE33,AE34=AE35),"b", IF(OR(AE34=AE33,AE34=AE35),"a", IF(AND(AE34=AE33,AE34<>AE35),"a","")))), IF(AF33="b", (IF(AND(AE34<>AE33,AE34=AE35),"c", IF(OR(AE34=AE33,AE34=AE35),"b", IF(AND(AE34=AE33,AE34<>AE35),"b","c")))), IF(AF33="c", (IF(AND(AE34<>AE33,AE34=AE35),"d", IF(OR(AE34=AE33,AE34=AE35),"c", IF(AND(AE34=AE33,AE34<>AE35),"c","d")))), IF(AF33="d", (IF(AND(AE34<>AE33,AE34=AE35),"e", IF(OR(AE34=AE33,AE34=AE35),"d", IF(AND(AE34=AE33,AE34<>AE35),"d","e")))),"Z"))))))))
AF35	AF35	=IF(AE35="","", IF(AND(COUNTIF(AG$27:AG27,"")=ROWS(AF$28:AF35)-1,AE35=AF36),"a", IF(AND(AE35<>AE34,AE35<>AF36),"", IF(AF34="", (IF(COUNTIF(AG$27:AG27,"a")=0,"a", IF(COUNTIF(AG$27:AG27,"b")=0,"b", IF(COUNTIF(AG$27:AG27,"c")=0,"c","d")))), IF(AF34="a", (IF(AND(AE35<>AE34,AE35=AF36),"b", IF(OR(AE35=AE34,AE35=AF36),"a", IF(AND(AE35=AE34,AE35<>AF36),"a","")))), IF(AF34="b", (IF(AND(AE35<>AE34,AE35=AF36),"c", IF(OR(AE35=AE34,AE35=AF36),"b", IF(AND(AE35=AE34,AE35<>AF36),"b","c")))), IF(AF34="c", (IF(AND(AE35<>AE34,AE35=AF36),"d", IF(OR(AE35=AE34,AE35=AF36),"c", IF(AND(AE35=AE34,AE35<>AF36),"c","d")))), IF(AF34="d", (IF(AND(AE35<>AE34,AE35=AF36),"e", IF(OR(AE35=AE34,AE35=AF36),"d", IF(AND(AE35=AE34,AE35<>AF36),"d","e")))),"Z"))))))))
Y28:Y35	Y28	=IF(AA28<>"","*","")
Y36	Y36	="ROUND "&$O28
Y38:Y45	Y38	=AF28
Z38:Z45	Z38	=IF(COUNTIF(Y$38:Y$45,$J7)<>0,COUNTIF(Y$38:Y$45,$J7),"")
AA38:AA45	AA38	=IF(Y38<>"",AG28,"")
AC38:AC45	AC38	=INDEX($U$27:$U$42,MATCH(AA38,$P$27:$P$42,0))
AD38	AD38	=IF(Y38="","",IF(ROWS(AD$38:AD38)=1,1, IF(AE37="",1,AE37+1)))
AE38:AE45	AE38	=IF(AA38<>"",IFERROR(INDEX(AC$38:AC$45,MATCH(AD38,AB$38:AB$45,0)),""),"")
AG38:AG45	AG38	=IF(AE38<>"",INDEX(AA$38:AA$45,MATCH(AD38,AB$38:AB$45,0)),"")
AD39:AD45	AD39	=IF(Y39="","",IF(ROWS(AD$38:AD39)=1,1, IF(AD38="",1,AD38+1)))
W27:W42	W27	=IFERROR(INDEX(Tab_TeamStat[ConfPct],MATCH($P27,Tab_TeamStat[Abr],0)),"---")
Press CTRL+SHIFT+ENTER to enter array formulas.