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)

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