Last 6 Events Based on Date

A

AYouQueTai

Board Regular
Joined
Sep 14, 2019
Messages
50
Office Version
  1. 365
Platform
  1. Windows
Hello,

I asked for help a few weeks ago and was given the following formula. It works great but I would like to tweak it a bit now that I see it's potential.

I would like it to check the date in cell F2 of my Matchup Sheet and get the 6 last completed results from that date. Results[Date] is the range it should check.
This would allow me to look back at previous games and see their last 6 results from any given night rather then just being able to see their last 6 games as of today.
The date would act as a blocker and not count any games after it.

=INDEX(Results[GameID],AGGREGATE(14,6,(ROW(Results[Visitor])-ROW('Results'!$E$2)+1)/((--(Results[Status]="Completed"))+(--($A$8=Results[Visitor]))+(--($A$8=Results[Home]))=2),ROWS($A38:$C$43)))

I tried adding (--(Results[Date]<$F$2)) but it didn't work. Dividing the aggregate function boggles my mind so i'm hoping someone can help.

NHL 2021.xlsx
ABCDEF
1GameIDStatusDateTimeVisitorHome
2020001CompletedJan-135:30 PMPittsburghPhiladelphia
3020002CompletedJan-138:00 PMChicagoTampa Bay
4020003CompletedJan-137:00 PMMontrealToronto
5020004CompletedJan-1310:00 PMVancouverEdmonton
6020005CompletedJan-1310:30 PMSt-LouisColorado
7020006CompletedJan-147:00 PMWashingtonBuffalo
8020007CompletedJan-147:00 PMBostonNew Jersey
9020008CompletedJan-147:00 PMNY IslandersNY Rangers
10020010CompletedJan-147:30 PMCarolinaDetroit
11020011CompletedJan-148:00 PMColumbusNashville
12020012CompletedJan-148:00 PMCalgaryWinnipeg
13020013CompletedJan-149:00 PMVancouverEdmonton
14020014CompletedJan-149:00 PMSan JoseArizona
15020015CompletedJan-1410:00 PMAnaheimVegas
16020016CompletedJan-1410:00 PMMinnesotaLos Angeles
17020017CompletedJan-157:00 PMWashingtonBuffalo
18020018CompletedJan-157:00 PMPittsburghPhiladelphia
19020019CompletedJan-157:00 PMChicagoTampa Bay
20020021CompletedJan-157:00 PMTorontoOttawa
21020022CompletedJan-159:00 PMSt-LouisColorado
22020023CompletedJan-161:00 PMBostonNew Jersey
23020024CompletedJan-167:00 PMNY IslandersNY Rangers
24020025CompletedJan-167:00 PMCarolinaDetroit
25020026CompletedJan-168:00 PMColumbusNashville
26020027CompletedJan-167:00 PMTorontoOttawa
27020028CompletedJan-1610:00 PMVancouverCalgary
28020029CompletedJan-167:00 PMMontrealEdmonton
29020030CompletedJan-164:00 PMSan JoseArizona
30020031CompletedJan-1610:00 PMAnaheimVegas
31020032CompletedJan-169:00 PMMinnesotaLos Angeles
32020034CompletedJan-177:00 PMChicagoFlorida
33020035CompletedJan-1712:00 PMWashingtonPittsburgh
34020036CompletedJan-185:00 PMBostonNY Islanders
35020037CompletedJan-187:30 PMBuffaloPhiladelphia
36020038CompletedJan-1812:00 PMColumbusDetroit
37020039CompletedJan-188:00 PMSan JoseSt-Louis
38020040CompletedJan-188:00 PMCarolinaNashville
39020041CompletedJan-187:00 PMWinnipegToronto
40020042CompletedJan-189:00 PMVancouverCalgary
41020043CompletedJan-189:00 PMMontrealEdmonton
42020044CompletedJan-1810:00 PMArizonaVegas
43020045CompletedJan-189:00 PMMinnesotaAnaheim
44020046CompletedJan-197:00 PMNew JerseyNY Rangers
45020047CompletedJan-197:00 PMBuffaloPhiladelphia
46020049CompletedJan-197:00 PMChicagoFlorida
47020050CompletedJan-197:00 PMWashingtonPittsburgh
48020051CompletedJan-197:30 PMColumbusDetroit
49020053CompletedJan-197:00 PMWinnipegOttawa
50020054CompletedJan-1910:00 PMColoradoLos Angeles
51020055CompletedJan-209:00 PMSan JoseSt-Louis
52020056CompletedJan-207:00 PMEdmontonToronto
53020057CompletedJan-2010:00 PMMontrealVancouver
54020058CompletedJan-2010:00 PMArizonaVegas
55020059CompletedJan-209:30 PMMinnesotaAnaheim
56020060CompletedJan-217:00 PMPhiladelphiaBoston
57020061CompletedJan-217:00 PMNew JerseyNY Islanders
58020063CompletedJan-217:00 PMTampa BayColumbus
59020064CompletedJan-217:00 PMWinnipegOttawa
60020065CompletedJan-219:30 PMMontrealVancouver
61020066CompletedJan-2110:00 PMColoradoLos Angeles
62020067CompletedJan-227:00 PMBuffaloWashington
63020068CompletedJan-227:00 PMNY RangersPittsburgh
64020070CompletedJan-227:00 PMEdmontonToronto
65020069CompletedJan-228:00 PMDetroitChicago
66020071CompletedJan-228:00 PMSan JoseMinnesota
67020072CompletedJan-228:30 PMNashvilleDallas
68020073CompletedJan-229:00 PMVegasArizona
69020074CompletedJan-2210:00 PMColoradoAnaheim
70020077CompletedJan-232:00 PMTampa BayColumbus
71020080CompletedJan-237:00 PMMontrealVancouver
72020075CompletedJan-237:00 PMPhiladelphiaBoston
73020078CompletedJan-238:00 PMLos AngelesSt-Louis
74020079CompletedJan-2310:00 PMOttawaWinnipeg
75020084CompletedJan-2412:30 PMDetroitChicago
76020082CompletedJan-243:00 PMBuffaloWashington
77020087CompletedJan-244:00 PMTorontoCalgary
78020090CompletedJan-244:00 PMVegasArizona
79020081CompletedJan-247:00 PMNY IslandersNew Jersey
80020083CompletedJan-247:00 PMNY RangersPittsburgh
81020091CompletedJan-248:00 PMColoradoAnaheim
82020085CompletedJan-248:00 PMLos AngelesSt-Louis
83020088CompletedJan-248:00 PMSan JoseMinnesota
84020089CompletedJan-248:00 PMNashvilleDallas
85020086CompletedJan-249:00 PMEdmontonWinnipeg
86020092CompletedJan-2510:00 PMOttawaVancouver
87020093CompletedJan-267:00 PMPittsburghBoston
88020094CompletedJan-267:00 PMNY RangersBuffalo
89020095CompletedJan-267:00 PMPhiladelphiaNew Jersey
90020096CompletedJan-267:00 PMNY IslandersWashington
91020098CompletedJan-267:00 PMFloridaColumbus
92020099CompletedJan-268:00 PMChicagoNashville
93020100CompletedJan-268:00 PMEdmontonWinnipeg
94020102CompletedJan-268:00 PMLos AngelesMinnesota
95020103CompletedJan-268:30 PMDetroitDallas
96020106CompletedJan-269:00 PMSt-LouisVegas
97020101CompletedJan-269:00 PMTorontoCalgary
98020104CompletedJan-269:00 PMSan JoseColorado
99020105CompletedJan-269:00 PMAnaheimArizona
100020107CompletedJan-277:30 PMChicagoNashville
Results
Cells with Data Validation
CellAllowCriteria
E2:F100List=INDIRECT("Teams[Team]")
B2:B100List=Dropdowns!$A$2:$A$3


Thanks
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Sort by date Sort by votes
Can you post your sheet where you want the result?
 
Upvote 0
Sure,

NHL 2021.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAU
1Game ID020251
2DateFeb-154:00 PM
31 X 2STLTIEARI
42.4204.1002.640
5Over/Under5.0OVERUNDER
61.7092.250
7
8St-LouisArizona
9
10
11Record - Overall
12GamesWinsLossesTiesPointsPPG
1316754181.13
1444%31%25%
15
16Record - Visitor
17GamesWinsLossesTiesPointsPPG
188422101.25
1950%25%25%
20
21Goals For
22GEVPPSHGPG
235041603.13
2482%12%0%
25
26Goals Against
27GAEVPKPPGPG
2847281605.88
2960%34%0%
30
31Special Teams
32TeamGoalsOpp.%PP PGTOI
33PP65311%3.314:53
34PK167077%4.386:09
35
36St-Louis - Last 6 GamesResultsPower PlayPenalty Kill
37Game IDDateVisitorGoalHomeGoalW/L/TOver?PP GFPP Opp.PP %PK GAPK OppPK %
38020169Feb-04Arizona4St-Louis3LossOver040%02100%
39020550Feb-06Arizona3St-Louis1LossUnder040%1786%
40020565Feb-08Arizona3St-Louis3TieOver1520%2560%
41020684Feb-12St-Louis4Arizona1WinRefund1250%03100%
42020239Feb-13St-Louis4Arizona4TieOver020%02100%
43020251Feb-15St-Louis0Arizona1LossUnder020%04100%
GameSheet
Cell Formulas
RangeFormula
F2F2=VLOOKUP($F$1,Schedule[#All],3,FALSE)
K2K2=VLOOKUP($F$1,Schedule[#All],4,FALSE)
L3L3=VLOOKUP(VLOOKUP($F$1,Schedule[#All],6,FALSE),Teams[#All],3,FALSE)
I4I4=IF(ISBLANK(VLOOKUP($F$1,Schedule[#All],16,FALSE)),VLOOKUP($F$1,Schedule[#All],10,FALSE),VLOOKUP($F$1,Schedule[#All],16,FALSE))
L4L4=IF(ISBLANK(VLOOKUP($F$1,Schedule[#All],17,FALSE)),VLOOKUP($F$1,Schedule[#All],11,FALSE),VLOOKUP($F$1,Schedule[#All],17,FALSE))
F3F3=VLOOKUP(VLOOKUP($F$1,Schedule[#All],5,FALSE),Teams[#All],3,FALSE)
F4F4=IF(ISBLANK(VLOOKUP($F$1,Schedule[#All],15,FALSE)),VLOOKUP($F$1,Schedule[#All],9,FALSE),VLOOKUP($F$1,Schedule[#All],15,FALSE))
F5F5=IF(ISBLANK(VLOOKUP($F$1,Schedule[#All],19,FALSE)),VLOOKUP($F$1,Schedule[#All],13,FALSE),VLOOKUP($F$1,Schedule[#All],19,FALSE))
I6I6=IF(ISBLANK(VLOOKUP($F$1,Schedule[#All],18,FALSE)),VLOOKUP($F$1,Schedule[#All],12,FALSE),VLOOKUP($F$1,Schedule[#All],18,FALSE))
L6L6=IF(ISBLANK(VLOOKUP($F$1,Schedule[#All],20,FALSE)),VLOOKUP($F$1,Schedule[#All],14,FALSE),VLOOKUP($F$1,Schedule[#All],20,FALSE))
A8A8=VLOOKUP($F$1,Schedule[#All],5,FALSE)
O8O8=VLOOKUP($F$1,Schedule[#All],6,FALSE)
A13A13=SUM(SUMPRODUCT((Results[Date]>=Dropdowns!$E$2)*(Results[Date]<=$F$2)*(Results[Status]="Completed")*(Results[Visitor]=$A$8)),SUMPRODUCT((Results[Date]>=Dropdowns!$E$2)*(Results[Date]<=$F$2)*(Results[Status]="Completed")*(Results[Home]=$A$8)))
D13D13=SUM(SUMPRODUCT((Results[Date]>=Dropdowns!$E$2)*(Results[Date]<=$F$2)*(Results[Status]="Completed")*(Results[Visitor]=$A$8)*(Results[VRes]="Win")),SUMPRODUCT((Results[Date]>=Dropdowns!$E$2)*(Results[Date]<=$F$2)*(Results[Status]="Completed")*(Results[Home]=$A$8)*(Results[HRes]="Win")))
G13G13=SUM(SUMPRODUCT((Results[Date]>=Dropdowns!$E$2)*(Results[Date]<=$F$2)*(Results[Status]="Completed")*(Results[Visitor]=$A$8)*(Results[VRes]="Loss")),SUMPRODUCT((Results[Date]>=Dropdowns!$E$2)*(Results[Date]<=$F$2)*(Results[Status]="Completed")*(Results[Home]=$A$8)*(Results[HRes]="Loss")))
J13J13=SUM(SUMPRODUCT((Results[Date]>=Dropdowns!$E$2)*(Results[Date]<=$F$2)*(Results[Status]="Completed")*(Results[Visitor]=$A$8)*(Results[VRes]="Tie")),SUMPRODUCT((Results[Date]>=Dropdowns!$E$2)*(Results[Date]<=$F$2)*(Results[Status]="Completed")*(Results[Home]=$A$8)*(Results[HRes]="Tie")))
M13,M18M13=(D13*2)+J13
P13,P18P13=M13/A13
D14,D29,D24,D19D14=D13/A13
G14,G29,G24,G19G14=G13/A13
J14,J29,J24,J19J14=J13/A13
A18A18=SUMPRODUCT((Results[Date]>=Dropdowns!$E$2)*(Results[Date]<=$F$2)*(Results[Status]="Completed")*(Results[Visitor]=$A$8))
D18D18=SUMPRODUCT((Results[Date]>=Dropdowns!$E$2)*(Results[Date]<=$F$2)*(Results[Status]="Completed")*(Results[Visitor]=$A$8)*(Results[VRes]="Win"))
G18G18=SUMPRODUCT((Results[Date]>=Dropdowns!$E$2)*(Results[Date]<=$F$2)*(Results[Status]="Completed")*(Results[Visitor]=$A$8)*(Results[VRes]="Loss"))
J18J18=SUMPRODUCT((Results[Date]>=Dropdowns!$E$2)*(Results[Date]<=$F$2)*(Results[Status]="Completed")*(Results[Visitor]=$A$8)*(Results[VRes]="Tie"))
A23A23=SUM(SUMPRODUCT((Goals[Date]>=Dropdowns!$E$2)*(Goals[Date]<=$F$2)*(Goals[GF Team]=$A$8)*(Goals[Period]=1)),SUMPRODUCT((Goals[Date]>=Dropdowns!$E$2)*(Goals[Date]<=$F$2)*(Goals[GF Team]=$A$8)*(Goals[Period]=2)),SUMPRODUCT((Goals[Date]>=Dropdowns!$E$2)*(Goals[Date]<=$F$2)*(Goals[GF Team]=$A$8)*(Goals[Period]=3)))
D23D23=SUM(SUMPRODUCT((Goals[Date]>=Dropdowns!$E$2)*(Goals[Date]<=$F$2)*(Goals[GF Team]=$A$8)*(Goals[Strenght]="EV")*(Goals[Period]=1)),SUMPRODUCT((Goals[Date]>=Dropdowns!$E$2)*(Goals[Date]<=$F$2)*(Goals[GF Team]=$A$8)*(Goals[Strenght]="EV")*(Goals[Period]=2)),SUMPRODUCT((Goals[Date]>=Dropdowns!$E$2)*(Goals[Date]<=$F$2)*(Goals[GF Team]=$A$8)*(Goals[Strenght]="EV")*(Goals[Period]=3)))
G23,D33G23=SUM(SUMPRODUCT((Goals[Date]>=Dropdowns!$E$2)*(Goals[Date]<=$F$2)*(Goals[GF Team]=$A$8)*(Goals[Strenght]="PP")*(Goals[Period]=1)),SUMPRODUCT((Goals[Date]>=Dropdowns!$E$2)*(Goals[Date]<=$F$2)*(Goals[GF Team]=$A$8)*(Goals[Strenght]="PP")*(Goals[Period]=2)),SUMPRODUCT((Goals[Date]>=Dropdowns!$E$2)*(Goals[Date]<=$F$2)*(Goals[GF Team]=$A$8)*(Goals[Strenght]="PP")*(Goals[Period]=3)))
J23J23=SUM(SUMPRODUCT((Goals[Date]>=Dropdowns!$E$2)*(Goals[Date]<=$F$2)*(Goals[GF Team]=$A$8)*(Goals[Strenght]="SH")*(Goals[Period]=1)),SUMPRODUCT((Goals[Date]>=Dropdowns!$E$2)*(Goals[Date]<=$F$2)*(Goals[GF Team]=$A$8)*(Goals[Strenght]="SH")*(Goals[Period]=2)),SUMPRODUCT((Goals[Date]>=Dropdowns!$E$2)*(Goals[Date]<=$F$2)*(Goals[GF Team]=$A$8)*(Goals[Strenght]="SH")*(Goals[Period]=3)))
M23,M28M23=A23/A13
A28A28=SUM(SUMPRODUCT((Goals[Date]>=Dropdowns!$E$2)*(Goals[Date]<=$F$2)*(Goals[GA Team]=$A$8)*(Goals[Period]=1)),SUMPRODUCT((Goals[Date]>=Dropdowns!$E$2)*(Goals[Date]<=$F$2)*(Goals[GA Team]=$A$8)*(Goals[Period]=2)),SUMPRODUCT((Goals[Date]>=Dropdowns!$E$2)*(Goals[Date]<=$F$2)*(Goals[GA Team]=$A$8)*(Goals[Period]=3)))
D28D28=SUM(SUMPRODUCT((Goals[Date]>=Dropdowns!$E$2)*(Goals[Date]<=$F$2)*(Goals[GA Team]=$A$8)*(Goals[Strenght]="EV")*(Goals[Period]=1)),SUMPRODUCT((Goals[Date]>=Dropdowns!$E$2)*(Goals[Date]<=$F$2)*(Goals[GA Team]=$A$8)*(Goals[Strenght]="EV")*(Goals[Period]=2)),SUMPRODUCT((Goals[Date]>=Dropdowns!$E$2)*(Goals[Date]<=$F$2)*(Goals[GA Team]=$A$8)*(Goals[Strenght]="EV")*(Goals[Period]=3)))
G28,D34G28=SUM(SUMPRODUCT((Goals[Date]>=Dropdowns!$E$2)*(Goals[Date]<=$F$2)*(Goals[GA Team]=$A$8)*(Goals[Strenght]="PP")*(Goals[Period]=1)),SUMPRODUCT((Goals[Date]>=Dropdowns!$E$2)*(Goals[Date]<=$F$2)*(Goals[GA Team]=$A$8)*(Goals[Strenght]="PP")*(Goals[Period]=2)),SUMPRODUCT((Goals[Date]>=Dropdowns!$E$2)*(Goals[Date]<=$F$2)*(Goals[GA Team]=$A$8)*(Goals[Strenght]="PP")*(Goals[Period]=3)))
J28J28=SUM(SUMPRODUCT((Goals[Date]>=Dropdowns!$E$2)*(Goals[Date]<=$F$2)*(Goals[GA Team]=$A$8)*(Goals[Strenght]="SH")*(Goals[Period]=1)),SUMPRODUCT((Goals[Date]>=Dropdowns!$E$2)*(Goals[Date]<=$F$2)*(Goals[GA Team]=$A$8)*(Goals[Strenght]="SH")*(Goals[Period]=2)),SUMPRODUCT((Goals[Date]>=Dropdowns!$E$2)*(Goals[Date]<=$F$2)*(Goals[GA Team]=$A$8)*(Goals[Strenght]="SH")*(Goals[Period]=3)))
G33G33=SUM(SUMPRODUCT((Penalties[Date]>=Dropdowns!$E$2)*(Penalties[Date]<=$F$2)*(Penalties[Visitor]=$A$8)*(Penalties[Home Pen])),SUMPRODUCT((Penalties[Date]>=Dropdowns!$E$2)*(Penalties[Date]<=$F$2)*(Penalties[Home]=$A$8)*(Penalties[Vis Pen])))
J33,AJ38:AJ43J33=D33/G33
M33M33=G33/A13
P33P33=SUM(SUMPRODUCT((Penalties[Date]>=Dropdowns!$E$2)*(Penalties[Date]<=$F$2)*(Penalties[Visitor]=$A$8)*(Penalties[Vis PP])),SUMPRODUCT((Penalties[Date]>=Dropdowns!$E$2)*(Penalties[Date]<=$F$2)*(Penalties[Home]=$A$8)*(Penalties[Home PP])))/$A$13
G34G34=SUM(SUMPRODUCT((Penalties[Date]>=Dropdowns!$E$2)*(Penalties[Date]<=$F$2)*(Penalties[Visitor]=$A$8)*(Penalties[Vis Pen])),SUMPRODUCT((Penalties[Date]>=Dropdowns!$E$2)*(Penalties[Date]<=$F$2)*(Penalties[Home]=$A$8)*(Penalties[Home Pen])))
J34,AS38:AS43J34=(G34-D34)/G34
M34M34=G34/A13
P34P34=SUM(SUMPRODUCT((Penalties[Date]>=Dropdowns!$E$2)*(Penalties[Date]<=$F$2)*(Penalties[Visitor]=$A$8)*(Penalties[Vis SH])),SUMPRODUCT((Penalties[Date]>=Dropdowns!$E$2)*(Penalties[Date]<=$F$2)*(Penalties[Home]=$A$8)*(Penalties[Home SH])))/$A$13
A36A36=CONCATENATE(A8," - ","Last 6 Games")
A38:A43A38=INDEX(Results[GameID],AGGREGATE(14,6,(ROW(Results[Visitor])-ROW('Results'!$E$2)+1)/((--(Results[Status]="Completed"))+(--($A$8=Results[Visitor]))+(--($A$8=Results[Home]))=2),ROWS($A38:$C$43)))
D38:D43D38=INDEX(Results[Date],AGGREGATE(14,6,(ROW(Results[Visitor])-ROW('Results'!$E$2)+1)/((--(Results[Status]="Completed"))+(--($A$8=Results[Visitor]))+(--($A$8=Results[Home]))=2),ROWS($D38:$G$43)))
H38:H43H38=INDEX(Results[Visitor],AGGREGATE(14,6,(ROW(Results[Visitor])-ROW('Results'!$E$2)+1)/((--(Results[Status]="Completed"))+(--($A$8=Results[Visitor]))+(--($A$8=Results[Home]))=2),ROWS($H38:$L$43)))
M38:M43M38=INDEX(Results[VGoals],AGGREGATE(14,6,(ROW(Results[Visitor])-ROW('Results'!$E$2)+1)/((--(Results[Status]="Completed"))+(--($A$8=Results[Visitor]))+(--($A$8=Results[Home]))=2),ROWS($M38:$O$43)))
P38:P43P38=INDEX(Results[Home],AGGREGATE(14,6,(ROW(Results[Visitor])-ROW('Results'!$E$2)+1)/((--(Results[Status]="Completed"))+(--($A$8=Results[Visitor]))+(--($A$8=Results[Home]))=2),ROWS($P38:$T$43)))
U38:U43U38=INDEX(Results[HGoals],AGGREGATE(14,6,(ROW(Results[Visitor])-ROW('Results'!$E$2)+1)/((--(Results[Status]="Completed"))+(--($A$8=Results[Visitor]))+(--($A$8=Results[Home]))=2),ROWS($U38:$W$43)))
X38:X43X38=IF($H38=$A$8,IF($M38=$U38,"Tie",IF($M38>$U38,"Win","Loss")),IF($U38=$M38,"Tie",IF($U38>$M38,"Win","Loss")))
AA38:AA43AA38=IF(SUM($M38,$U38)=$F$5,"Refund",IF(SUM($M38,$U38)>$F$5,"Over","Under"))
AD38:AD43AD38=SUM(SUMPRODUCT((Goals[Game ID]=$A38)*(Goals[Period]=1)*(Goals[Strenght]="PP")*(Goals[GF Team]=$A$8)),SUMPRODUCT((Goals[Game ID]=$A38)*(Goals[Period]=2)*(Goals[Strenght]="PP")*(Goals[GF Team]=$A$8)),SUMPRODUCT((Goals[Game ID]=$A38)*(Goals[Period]=3)*(Goals[Strenght]="PP")*(Goals[GF Team]=$A$8)))
AG38:AG43AG38=IF($H38=$A$8,VLOOKUP($A38,Penalties[#All],12,FALSE),IF($P38=$A$8,VLOOKUP($A38,Penalties[#All],11,FALSE),""))
AM38:AM43AM38=SUM(SUMPRODUCT((Goals[Game ID]=$A38)*(Goals[Period]=1)*(Goals[Strenght]="PP")*(Goals[GA Team]=$A$8)),SUMPRODUCT((Goals[Game ID]=$A38)*(Goals[Period]=2)*(Goals[Strenght]="PP")*(Goals[GA Team]=$A$8)),SUMPRODUCT((Goals[Game ID]=$A38)*(Goals[Period]=3)*(Goals[Strenght]="PP")*(Goals[GA Team]=$A$8)))
AP38:AP43AP38=IF($H38=$A$8,VLOOKUP($A38,Penalties[#All],11,FALSE),IF($P38=$A$8,VLOOKUP($A38,Penalties[#All],12,FALSE),""))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F4:H4Expression=VLOOKUP($F$1,'Results'!$A$1:$I$5000,7,FALSE)="Tie"textNO
F4:H4Expression=VLOOKUP($F$1,'Results'!$A$1:$I$5000,7,FALSE)="Win"textNO
F4:H4Expression=VLOOKUP($F$1,'Results'!$A$1:$I$5000,7,FALSE)="Loss"textNO
I4:K4Expression=VLOOKUP($F$1,'Results'!$A$1:$I$5000,7,FALSE)="Win"textNO
I4:K4Expression=VLOOKUP($F$1,'Results'!$A$1:$I$5000,7,FALSE)="Loss"textNO
I4:K4Expression=VLOOKUP($F$1,'Results'!$A$1:$I$5000,7,FALSE)="Tie"textNO
L4:N4Expression=VLOOKUP($F$1,'Results'!$A$1:$I$5000,10,FALSE)="Tie"textNO
L4:N4Expression=VLOOKUP($F$1,'Results'!$A$1:$I$5000,10,FALSE)="Win"textNO
L4:N4Expression=VLOOKUP($F$1,'Results'!$A$1:$I$5000,10,FALSE)="Loss"textNO
I6:K6Expression=VLOOKUP($F$1,'Results'!$A$1:$N$5000,15,FALSE)="Under"textNO
I6:K6Expression=VLOOKUP($F$1,'Results'!$A$1:$N$5000,15,FALSE)="Over"textNO
L6:N6Expression=VLOOKUP($F$1,'Results'!$A$1:$N$5000,15,FALSE)="Over"textNO
L6:N6Expression=VLOOKUP($F$1,'Results'!$A$1:$N$5000,15,FALSE)="Under"textNO
 
Upvote 0
Thanks for that, try
Excel Formula: 
=IFERROR(INDEX(Results[GameID],AGGREGATE(14,6,(ROW(Results[Visitor])-ROW('Results'!$E$2)+1)/(Results[Status]="Completed")/(Results[Date]<=$F$2)/(($A$8=Results[Visitor])+($A$8=Results[Home])),ROWS($A38:$C$43))),"")
 
Upvote 0
Solution
You're welcome & thanks for the feedback.
 
Upvote 0
Just noticed a small issue, is there anyway to remove the = from (Results[Date]<=$F$2). It shouldn't include games that are played that night

I tried modifying it to (Results[Date]<$F$2) and (Results[Date]<=$F$2-1) but it through off the results and didn't fetch the right games
 
Upvote 0
lol I feel so smart right now.
I swear I tried it and the list of numbers was off so I thought it messed up the division or something.
Just tried it again and it works great. Thanks again
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Formula to split date and time and work out energy usage trend
Replies
6
Views
470
Sundance_Kid
S
K
  • Question Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
607
awoohaw
awoohaw
M
  • Solved
Returning results based on multiple criteria
Replies
5
Views
499
DanteAmor
DanteAmor
E
  • Question Question
Average a range between 2 dates
Replies
3
Views
545
Fluff
Fluff
R
  • Question Question
Highlighting cells based on criteria
Replies
0
Views
443
rahildhody
R

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

Share this page

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