INDEX - AGGREGATE - ROWS

A

AYouQueTai

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

I am trying to make a lookup sheet that would list the "season series" between 2 teams. Meaning any time Montreal has faced Vancouver (whether they were visiting or at home) AND the status is "Completed"
I would like the Game ID. the Date, Visitor, Home to be listed in the summary sheet, sorted by oldest date at the top. Some teams will face each other 10 times so I plan on leaving 10 rows open for the results.
Here is a sample of my data sheet and what the lookup sheet would look like.
Does anyone have a formula that would do this? Thanks

OverUnder2021.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
Schedule
Cells with Data Validation
CellAllowCriteria
F1:F28List=INDIRECT("Teams[Team]")
E2:E28List=INDIRECT("Teams[Team]")
B2:B28List=Dropdowns!$A$2:$A$4



Book1
ABCDEFGH
1GameIDStatusDateVisitorHomeTeam 1Team 2
2020057CompletedJan-20MontrealVancouverMontrealVancouver
3020065CompletedJan-21MontrealVancouver
4020080CompletedJan-23MontrealVancouver
5020147CompletedFeb-01VancouverMontreal
6020155CompletedFeb-02VancouverMontreal
7
8
9
10
11
Sheet1
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
Maybe
Excel Formula: 
=IFERROR(INDEX(Sheet2!A$2:A$28,AGGREGATE(15,6,(ROW(Sheet2!$E$2:$E$28)-ROW(Sheet2!$E$2)+1)/((Sheet2!$E$2:$E$28=$G$2)+(Sheet2!$E$2:$E$28=$H$2))/((Sheet2!$F$2:$F$28=$G$2)+(Sheet2!$F$2:$F$28=$H$2))/(Sheet2!$B$2:$B$28="Completed"),ROWS(A$2:A2))),"")
 
Upvote 0
Solution
Since your date is already sorted oldest to newest

How about this!

Excel Message board.xlsm
ABCDEFGHIJKLMNO
1GameIDStatusDateTimeVisitorHomeGameIDStatusDateVisitorHomeTeam 1Team 2
220001Completed13/01/20210.729166667PittsburghPhiladelphia20004Completed13/01/2021VancouverEdmontonVancouverEdmonton
320002Completed13/01/20210.833333333ChicagoTampa Bay20013Completed14/01/2021VancouverEdmonton
420003Completed13/01/20210.791666667MontrealToronto     
520004Completed13/01/20210.916666667VancouverEdmonton     
620005Completed13/01/20210.9375St-LouisColorado
720006Completed14/01/20210.791666667WashingtonBuffalo
820007Completed14/01/20210.791666667BostonNew Jersey
920008Completed14/01/20210.791666667NY IslandersNY Rangers
1020010Completed14/01/20210.8125CarolinaDetroit
1120011Completed14/01/20210.833333333ColumbusNashville
1220012Completed14/01/20210.833333333CalgaryWinnipeg
1320013Completed14/01/20210.875VancouverEdmonton
1420014Completed14/01/20210.875San JoseArizona
1520015Completed14/01/20210.916666667AnaheimVegas
1620016Completed14/01/20210.916666667MinnesotaLos Angeles
1720017Completed15/01/20210.791666667WashingtonBuffalo
1820018Completed15/01/20210.791666667PittsburghPhiladelphia
1920019Completed15/01/20210.791666667ChicagoTampa Bay
2020021Completed15/01/20210.791666667TorontoOttawa
2120022Completed15/01/20210.875St-LouisColorado
2220023Completed16/01/20210.541666667BostonNew Jersey
2320024Completed16/01/20210.791666667NY IslandersNY Rangers
2420025Completed16/01/20210.791666667CarolinaDetroit
2520026Completed16/01/20210.833333333ColumbusNashville
2620027Completed16/01/20210.791666667TorontoOttawa
2720028Completed16/01/20210.916666667VancouverCalgary
2820029Completed16/01/20210.791666667MontrealEdmonton
Sheet3
Cell Formulas
RangeFormula
H2:J5H2=IFERROR(INDEX(A$2:A$28,AGGREGATE(15,6,ROW($A$2:$A$28)-ROW($A$2)+1/((($E$2:$E$28&$F$2:$F$28=$N$2&$O$2)+($E$2:$E$28&$F$2:$F$28=$O$2&$N$2))*($B$2:$B$28=$B$2)),ROWS($H$2:H2))),"")
K2:L5K2=IFERROR(INDEX(E$2:E$28,AGGREGATE(15,6,ROW($A$2:$A$28)-ROW($A$2)+1/((($E$2:$E$28&$F$2:$F$28=$N$2&$O$2)+($E$2:$E$28&$F$2:$F$28=$O$2&$N$2))*($B$2:$B$28=$B$2)),ROWS($H$2:K2))),"")
Cells with Data Validation
CellAllowCriteria
N2List=$E$2:$E$28
O2List=$F$2:$F$28
 
Upvote 0
alz said:
Since your date is already sorted oldest to newest

How about this!

Excel Message board.xlsm
ABCDEFGHIJKLMNO
1GameIDStatusDateTimeVisitorHomeGameIDStatusDateVisitorHomeTeam 1Team 2
220001Completed13/01/20210.729166667PittsburghPhiladelphia20004Completed13/01/2021VancouverEdmontonVancouverEdmonton
320002Completed13/01/20210.833333333ChicagoTampa Bay20013Completed14/01/2021VancouverEdmonton
420003Completed13/01/20210.791666667MontrealToronto     
520004Completed13/01/20210.916666667VancouverEdmonton     
620005Completed13/01/20210.9375St-LouisColorado
720006Completed14/01/20210.791666667WashingtonBuffalo
820007Completed14/01/20210.791666667BostonNew Jersey
920008Completed14/01/20210.791666667NY IslandersNY Rangers
1020010Completed14/01/20210.8125CarolinaDetroit
1120011Completed14/01/20210.833333333ColumbusNashville
1220012Completed14/01/20210.833333333CalgaryWinnipeg
1320013Completed14/01/20210.875VancouverEdmonton
1420014Completed14/01/20210.875San JoseArizona
1520015Completed14/01/20210.916666667AnaheimVegas
1620016Completed14/01/20210.916666667MinnesotaLos Angeles
1720017Completed15/01/20210.791666667WashingtonBuffalo
1820018Completed15/01/20210.791666667PittsburghPhiladelphia
1920019Completed15/01/20210.791666667ChicagoTampa Bay
2020021Completed15/01/20210.791666667TorontoOttawa
2120022Completed15/01/20210.875St-LouisColorado
2220023Completed16/01/20210.541666667BostonNew Jersey
2320024Completed16/01/20210.791666667NY IslandersNY Rangers
2420025Completed16/01/20210.791666667CarolinaDetroit
2520026Completed16/01/20210.833333333ColumbusNashville
2620027Completed16/01/20210.791666667TorontoOttawa
2720028Completed16/01/20210.916666667VancouverCalgary
2820029Completed16/01/20210.791666667MontrealEdmonton
Sheet3
Cell Formulas
RangeFormula
H2:J5H2=IFERROR(INDEX(A$2:A$28,AGGREGATE(15,6,ROW($A$2:$A$28)-ROW($A$2)+1/((($E$2:$E$28&$F$2:$F$28=$N$2&$O$2)+($E$2:$E$28&$F$2:$F$28=$O$2&$N$2))*($B$2:$B$28=$B$2)),ROWS($H$2:H2))),"")
K2:L5K2=IFERROR(INDEX(E$2:E$28,AGGREGATE(15,6,ROW($A$2:$A$28)-ROW($A$2)+1/((($E$2:$E$28&$F$2:$F$28=$N$2&$O$2)+($E$2:$E$28&$F$2:$F$28=$O$2&$N$2))*($B$2:$B$28=$B$2)),ROWS($H$2:K2))),"")
Cells with Data Validation
CellAllowCriteria
N2List=$E$2:$E$28
O2List=$F$2:$F$28
Click to expand...
I tried modifying your formula to my table ranges but now the formula evaluates to an error and leaves the cell blank.

=IFERROR(INDEX(Schedule[Visitor],AGGREGATE(15,6,ROW(Schedule[GameID])-ROW(Schedule!$A$2)+1/(((Schedule[Visitor]&Schedule[Home]=$A$5&$N$5)+(Schedule[Visitor]&Schedule[Home]=$A$5&$N$5))*(Schedule[Status]="Completed")),ROWS($A$53:D53))),"")

Am I doing something wrong?
 
Upvote 0
Try this!

=IFERROR(INDEX(Schedule[Visitor],AGGREGATE(15,6,ROW(Schedule[GameID])-ROW(Schedule![#Headers])/(((Schedule[Visitor]&Schedule[Home]=$A$5&$N$5)+(Schedule[Visitor]&Schedule[Home]=$A$5&$N$5))*(Schedule[Status]="Completed")),ROWS($A$53:D53))),"")
 
Upvote 0
Fluff said:
Maybe
Excel Formula: 
=IFERROR(INDEX(Sheet2!A$2:A$28,AGGREGATE(15,6,(ROW(Sheet2!$E$2:$E$28)-ROW(Sheet2!$E$2)+1)/((Sheet2!$E$2:$E$28=$G$2)+(Sheet2!$E$2:$E$28=$H$2))/((Sheet2!$F$2:$F$28=$G$2)+(Sheet2!$F$2:$F$28=$H$2))/(Sheet2!$B$2:$B$28="Completed"),ROWS(A$2:A2))),"")[/
[/QUOTE]
Click to expand...
Thank you very much, it works great
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0
alz said:
Try this!

=IFERROR(INDEX(Schedule[Visitor],AGGREGATE(15,6,ROW(Schedule[GameID])-ROW(Schedule![#Headers])/(((Schedule[Visitor]&Schedule[Home]=$A$5&$N$5)+(Schedule[Visitor]&Schedule[Home]=$A$5&$N$5))*(Schedule[Status]="Completed")),ROWS($A$53:D53))),"")
Click to expand...
Thank you very much for the help. Works great
 
  • Like
Reactions: alz
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
460
Sundance_Kid
S
K
  • Question Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
600
awoohaw
awoohaw
M
  • Solved
Returning results based on multiple criteria
Replies
5
Views
497
DanteAmor
DanteAmor
A
  • Question Question
NHL Hockey Days between games counter
Replies
0
Views
230
AYouQueTai
A
A
  • Solved
Countifs? Count events in a range if something doesn't happen in another range
Replies
2
Views
355
AYouQueTai
A

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

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