Offset ?

[gtd526]

Hello,
I want to bring the Avg Pts for the Team listed in (AE). Thank you.
Here is the sheet that determines the Team name. Sheet name (Favs)

NBA.xlsm
	AE	AF	AG
2			Avg
3	Teams	Injuries	Pts
4	Tie	6
5	Tie	8
6	TOR	2
7		7
8	Tie	1
9	Tie	3
10		2
11	UTA	3
12	BOS	5
13		3
14		4
15	WAS	4
Favs

Cell Formulas
Range	Formula
AE4	AE4	=IFS($AD4>$AD5,$A4,$AD4<$AD5,"",AND($AD4=0,$AD5=0),"",$AD4=$AD5,"Tie")
AF4:AF15	AF4	=IFERROR(INDEX(Injuries!$2:$2,MATCH($A$4:$A$29,Injuries!$1:$1,0)),"")
AE5	AE5	=IFS($AD5>$AD4,$A5,$AD5<$AD4,"",AND($AD5=0,$AD4=0),"",$AD5=$AD4,"Tie")
AE6,AE14,AE12,AE10,AE8	AE6	=IFS($AD6>$AD7,$A6,OR($AD6<$AD7,AND($AD6=0,$AD7=0)),"",$AD6=$AD7,"Tie")
AE7,AE15,AE13,AE11,AE9	AE7	=IFS($AD7>$AD6,$A7,OR($AD7<$AD6,AND($AD7=0,$AD6=0)),"",$AD7=$AD6,"Tie")

Here is the 2nd sheet that has the Avg Pts for each team. Sheet name (Injuries). The Range goes from B:BV.

NBA.xlsm
	B	C	D	E	F	G
1	WAS			POR
2	4	FG%	Pts	4	FG%	Pts
3	Thomas Bryant	65%	143	Zach Collins
4	Rui Hachimura	46%	113	CJ Elleby	54%	19
5	Moritz Wagner	72%	49	CJ McCollum	47%	347
6	Russell Westbrook	41%	230	Jusuf Nurkić	49%	118
7
8
9
10
11
12
13
14
15
16		Avg:	134		Avg:	161
Injuries

Cell Formulas
Range	Formula
B1,E1	B1	=LOOKUP(2, 1/((COUNTIF($B$1:B1,Favs!$A$4:$A$29)=0)*(Favs!$A$4:$A$29<>"")),Favs!$A$4:$A$29)
B2,E2	B2	=COUNTIF(InjuryList!$A:$A,B1)
B3,E3	B3	=IFERROR(INDEX(InjuryList!$B$3:$B$202,SMALL(IF(B$1=InjuryList!$A$3:$A$200,ROW(InjuryList!$A$3:$A$200)-ROW(InjuryList!$A$3)+1),1)),"")
F3:F15,C3:C15	C3	=IFERROR(VLOOKUP(B3,Player_Totals_Table[[Player]:[FG%]],10,0),"")
B4,E4	B4	=IFERROR(INDEX(InjuryList!$B$3:$B$202,SMALL(IF(B$1=InjuryList!$A$3:$A$200,ROW(InjuryList!$A$3:$A$200)-ROW(InjuryList!$A$3)+1),2)),"")
B5,E5	B5	=IFERROR(INDEX(InjuryList!$B$3:$B$202,SMALL(IF(B$1=InjuryList!$A$3:$A$200,ROW(InjuryList!$A$3:$A$200)-ROW(InjuryList!$A$3)+1),3)),"")
B6,E6	B6	=IFERROR(INDEX(InjuryList!$B$3:$B$202,SMALL(IF(B$1=InjuryList!$A$3:$A$200,ROW(InjuryList!$A$3:$A$200)-ROW(InjuryList!$A$3)+1),4)),"")
B7,E7	B7	=IFERROR(INDEX(InjuryList!$B$3:$B$202,SMALL(IF(B$1=InjuryList!$A$3:$A$200,ROW(InjuryList!$A$3:$A$200)-ROW(InjuryList!$A$3)+1),5)),"")
B8,E8	B8	=IFERROR(INDEX(InjuryList!$B$3:$B$202,SMALL(IF(B$1=InjuryList!$A$3:$A$200,ROW(InjuryList!$A$3:$A$200)-ROW(InjuryList!$A$3)+1),6)),"")
B9,E9	B9	=IFERROR(INDEX(InjuryList!$B$3:$B$202,SMALL(IF(B$1=InjuryList!$A$3:$A$200,ROW(InjuryList!$A$3:$A$200)-ROW(InjuryList!$A$3)+1),7)),"")
B10,E10	B10	=IFERROR(INDEX(InjuryList!$B$3:$B$202,SMALL(IF(B$1=InjuryList!$A$3:$A$200,ROW(InjuryList!$A$3:$A$200)-ROW(InjuryList!$A$3)+1),8)),"")
B11,E11	B11	=IFERROR(INDEX(InjuryList!$B$3:$B$202,SMALL(IF(B$1=InjuryList!$A$3:$A$200,ROW(InjuryList!$A$3:$A$200)-ROW(InjuryList!$A$3)+1),9)),"")
B12,E12	B12	=IFERROR(INDEX(InjuryList!$B$3:$B$202,SMALL(IF(B$1=InjuryList!$A$3:$A$200,ROW(InjuryList!$A$3:$A$200)-ROW(InjuryList!$A$3)+1),10)),"")
B13,E13	B13	=IFERROR(INDEX(InjuryList!$B$3:$B$202,SMALL(IF(B$1=InjuryList!$A$3:$A$200,ROW(InjuryList!$A$3:$A$200)-ROW(InjuryList!$A$3)+1),11)),"")
B14,E14	B14	=IFERROR(INDEX(InjuryList!$B$3:$B$202,SMALL(IF(B$1=InjuryList!$A$3:$A$200,ROW(InjuryList!$A$3:$A$200)-ROW(InjuryList!$A$3)+1),12)),"")
B15,E15	B15	=IFERROR(INDEX(InjuryList!$B$3:$B$202,SMALL(IF(B$1=InjuryList!$A$3:$A$200,ROW(InjuryList!$A$3:$A$200)-ROW(InjuryList!$A$3)+1),13)),"")
G3:G15,D3:D15	D3	=IFERROR(VLOOKUP(B3,Player_Totals_Table[[Player]:[PTS]],29,0),"")
D16,G16	D16	=IFERROR(AVERAGEIFS(D3:D15,D3:D15,"<>0"),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[KRice]

A couple of comments...
1. You might consider changing your INDEX/SMALL construction to
=IFERROR(INDEX(InjuryList!$B$3:$B$202,AGGREGATE(15,6,(ROW(InjuryList!$A$3:$A$202)-ROW(InjuryList!$A$2))/(B$1=InjuryList!$A$3:$A$202),ROWS(B$3:B3))),"")
entered in B3 and copied down and then copied into each subsequent name column (E3 and down, etc.)
That will eliminate the need for using an array formula and the ROWS function at the end of the formula will update as the formula is pulled down to occupy the 13 placeholder cells for player names, giving 1 for the 1st instance, 2 for the 2nd, and so on. AGGREGATE(15 means to return the smallest nth value.

2. OFFSET would be one way of pulling over the average, but I would favor INDEX/MATCH here in AG4 and down on the Favs sheet:
=INDEX(Injuries!$B$16:$BV$16,MATCH(AE4,Injuries!$B$1:$BV$1,0)+2)

One question though...this will give an error if a match is not found, so what is meant by TIE and blanks?
You'll probably want to wrap this with some type of check or trap to avoid the problem. Perhaps...
=IF((AE4<>"")*(AE4<>"TIE"),INDEX(Injuries!$B$16:$BV$16,MATCH(AE4,Injuries!$B$1:$BV$1,0)+2),"")

 

[gtd526]

A couple of comments...
1. You might consider changing your INDEX/SMALL construction to
=IFERROR(INDEX(InjuryList!$B$3:$B$202,AGGREGATE(15,6,(ROW(InjuryList!$A$3:$A$202)-ROW(InjuryList!$A$2))/(B$1=InjuryList!$A$3:$A$202),ROWS(B$3:B3))),"")
entered in B3 and copied down and then copied into each subsequent name column (E3 and down, etc.)
That will eliminate the need for using an array formula and the ROWS function at the end of the formula will update as the formula is pulled down to occupy the 13 placeholder cells for player names, giving 1 for the 1st instance, 2 for the 2nd, and so on. AGGREGATE(15 means to return the smallest nth value.

2. OFFSET would be one way of pulling over the average, but I would favor INDEX/MATCH here in AG4 and down on the Favs sheet:
=INDEX(Injuries!$B$16:$BV$16,MATCH(AE4,Injuries!$B$1:$BV$1,0)+2)

One question though...this will give an error if a match is not found, so what is meant by TIE and blanks?
You'll probably want to wrap this with some type of check or trap to avoid the problem. Perhaps...
=IF((AE4<>"")*(AE4<>"TIE"),INDEX(Injuries!$B$16:$BV$16,MATCH(AE4,Injuries!$B$1:$BV$1,0)+2),"")

Thank you for the reply. Your formula works great for "offset". I implemented the other changes you mentioned. Thank you.

 

[KRice]

You're welcome...I'm happy to help.