AYouQueTai
Board Regular
- Joined
- Sep 14, 2019
- Messages
- 50
- Office Version
- 365
- Platform
- 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
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 | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | GameID | Status | Date | Time | Visitor | Home | ||
2 | 020001 | Completed | Jan-13 | 5:30 PM | Pittsburgh | Philadelphia | ||
3 | 020002 | Completed | Jan-13 | 8:00 PM | Chicago | Tampa Bay | ||
4 | 020003 | Completed | Jan-13 | 7:00 PM | Montreal | Toronto | ||
5 | 020004 | Completed | Jan-13 | 10:00 PM | Vancouver | Edmonton | ||
6 | 020005 | Completed | Jan-13 | 10:30 PM | St-Louis | Colorado | ||
7 | 020006 | Completed | Jan-14 | 7:00 PM | Washington | Buffalo | ||
8 | 020007 | Completed | Jan-14 | 7:00 PM | Boston | New Jersey | ||
9 | 020008 | Completed | Jan-14 | 7:00 PM | NY Islanders | NY Rangers | ||
10 | 020010 | Completed | Jan-14 | 7:30 PM | Carolina | Detroit | ||
11 | 020011 | Completed | Jan-14 | 8:00 PM | Columbus | Nashville | ||
12 | 020012 | Completed | Jan-14 | 8:00 PM | Calgary | Winnipeg | ||
13 | 020013 | Completed | Jan-14 | 9:00 PM | Vancouver | Edmonton | ||
14 | 020014 | Completed | Jan-14 | 9:00 PM | San Jose | Arizona | ||
15 | 020015 | Completed | Jan-14 | 10:00 PM | Anaheim | Vegas | ||
16 | 020016 | Completed | Jan-14 | 10:00 PM | Minnesota | Los Angeles | ||
17 | 020017 | Completed | Jan-15 | 7:00 PM | Washington | Buffalo | ||
18 | 020018 | Completed | Jan-15 | 7:00 PM | Pittsburgh | Philadelphia | ||
19 | 020019 | Completed | Jan-15 | 7:00 PM | Chicago | Tampa Bay | ||
20 | 020021 | Completed | Jan-15 | 7:00 PM | Toronto | Ottawa | ||
21 | 020022 | Completed | Jan-15 | 9:00 PM | St-Louis | Colorado | ||
22 | 020023 | Completed | Jan-16 | 1:00 PM | Boston | New Jersey | ||
23 | 020024 | Completed | Jan-16 | 7:00 PM | NY Islanders | NY Rangers | ||
24 | 020025 | Completed | Jan-16 | 7:00 PM | Carolina | Detroit | ||
25 | 020026 | Completed | Jan-16 | 8:00 PM | Columbus | Nashville | ||
26 | 020027 | Completed | Jan-16 | 7:00 PM | Toronto | Ottawa | ||
27 | 020028 | Completed | Jan-16 | 10:00 PM | Vancouver | Calgary | ||
28 | 020029 | Completed | Jan-16 | 7:00 PM | Montreal | Edmonton | ||
Schedule |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
F1:F28 | List | =INDIRECT("Teams[Team]") |
E2:E28 | List | =INDIRECT("Teams[Team]") |
B2:B28 | List | =Dropdowns!$A$2:$A$4 |
Book1 | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | |||
1 | GameID | Status | Date | Visitor | Home | Team 1 | Team 2 | |||
2 | 020057 | Completed | Jan-20 | Montreal | Vancouver | Montreal | Vancouver | |||
3 | 020065 | Completed | Jan-21 | Montreal | Vancouver | |||||
4 | 020080 | Completed | Jan-23 | Montreal | Vancouver | |||||
5 | 020147 | Completed | Feb-01 | Vancouver | Montreal | |||||
6 | 020155 | Completed | Feb-02 | Vancouver | Montreal | |||||
7 | ||||||||||
8 | ||||||||||
9 | ||||||||||
10 | ||||||||||
11 | ||||||||||
Sheet1 |