# Offset ?

#### gtd526

##### Active Member
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
AEAFAG
2Avg
3TeamsInjuriesPts
4Tie6
5Tie8
6TOR2
7 7
8Tie1
9Tie3
10 2
11UTA3
12BOS5
13 3
14 4
15WAS4
Favs
Cell Formulas
RangeFormula
AF4:AF15AF4=IFERROR(INDEX(Injuries!\$2:\$2,MATCH(\$A\$4:\$A\$29,Injuries!\$1:\$1,0)),"")

Here is the 2nd sheet that has the Avg Pts for each team. Sheet name (Injuries). The Range goes from B:BV.
Cell Formulas
RangeFormula
B1,E1B1=LOOKUP(2, 1/((COUNTIF(\$B\$1:B1,Favs!\$A\$4:\$A\$29)=0)*(Favs!\$A\$4:\$A\$29<>"")),Favs!\$A\$4:\$A\$29)
B2,E2B2=COUNTIF(InjuryList!\$A:\$A,B1)
B3,E3B3=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:C15C3=IFERROR(VLOOKUP(B3,Player_Totals_Table[[Player]:[FG%]],10,0),"")
B4,E4B4=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,E5B5=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,E6B6=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,E7B7=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,E8B8=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,E9B9=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,E10B10=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,E11B11=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,E12B12=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,E13B13=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,E14B14=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,E15B15=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:D15D3=IFERROR(VLOOKUP(B3,Player_Totals_Table[[Player]:[PTS]],29,0),"")
D16,G16D16=IFERROR(AVERAGEIFS(D3:D15,D3:D15,"<>0"),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### KRice

##### Well-known Member
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

##### Active Member
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

##### Well-known Member
You're welcome...I'm happy to help.

Replies
7
Views
179
Replies
6
Views
153
Replies
0
Views
61
Replies
13
Views
153
Replies
4
Views
100

1,127,529
Messages
5,625,351
Members
416,096
Latest member
forevans

### 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.

### Which adblocker are you using?

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

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