Last 6 Events Based on Date

A

AYouQueTai

New Member
Joined
Sep 14, 2019
Messages
34
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

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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

K
  • Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
301
awoohaw
awoohaw
M
  • Solved
Returning results based on multiple criteria
Replies
5
Views
260
DanteAmor
DanteAmor
E
  • Question
Average a range between 2 dates
Replies
3
Views
345
Fluff
Fluff
M
  • Solved
Return Value for Day of Week Interval based on Start / Stop time
Replies
2
Views
414
Marklarbear
M
B
  • Solved
Index, match giving wrong results
Replies
3
Views
403
Fluff
Fluff

Forum statistics

Threads
1,214,613
Messages
6,120,515
Members
448,968
Latest member
Ajax40

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