Index, Match when values the same

[Grunter31]

I'm trying to setup a roster. In Cell K1 I have the date (which changes through drop down box). In column A I have a list of dates which some are the same date, Column C has Team 1 and Column E has Team 2

What I'm needing is when I change the date in Cell K1 it only shows Team A on that date in Cell K2 and Team 2 in Cell L2.

Tue, Oct 22, 2019	8:00p	New Orleans Pelicans		Toronto Raptors
Tue, Oct 22, 2019	10:30p	Los Angeles Lakers		Los Angeles Clippers
Wed, Oct 23, 2019	7:00p	Cleveland Cavaliers		Orlando Magic
Wed, Oct 23, 2019	7:00p	Detroit Pistons		Indiana Pacers
Wed, Oct 23, 2019	7:00p	Chicago Bulls		Charlotte Hornets
Wed, Oct 23, 2019	7:30p	Memphis Grizzlies		Miami Heat
Wed, Oct 23, 2019	7:30p	Minnesota Timberwolves		Brooklyn Nets
Wed, Oct 23, 2019	7:30p	Boston Celtics		Philadelphia 76ers
Wed, Oct 23, 2019	8:30p	Washington Wizards		Dallas Mavericks
Wed, Oct 23, 2019	8:30p	New York Knicks		San Antonio Spurs
Wed, Oct 23, 2019	9:00p	Oklahoma City Thunder		Utah Jazz
Wed, Oct 23, 2019	10:00p	Denver Nuggets		Portland Trail Blazers
Wed, Oct 23, 2019	10:00p	Sacramento Kings		Phoenix Suns
Thu, Oct 24, 2019	7:00p	Atlanta Hawks		Detroit Pistons
Thu, Oct 24, 2019	8:00p	Milwaukee Bucks		Houston Rockets
Thu, Oct 24, 2019	10:30p	Los Angeles Clippers		Golden State Warriors
Fri, Oct 25, 2019	7:00p	Toronto Raptors		Boston Celtics
Fri, Oct 25, 2019	7:00p	Minnesota Timberwolves		Charlotte Hornets
Fri, Oct 25, 2019	7:30p	New York Knicks		Brooklyn Nets
Fri, Oct 25, 2019	8:00p	Washington Wizards		Oklahoma City Thunder
Fri, Oct 25, 2019	8:00p	Dallas Mavericks		New Orleans Pelicans
Fri, Oct 25, 2019	8:00p	Chicago Bulls		Memphis Grizzlies
Fri, Oct 25, 2019	9:00p	Phoenix Suns		Denver Nuggets
Fri, Oct 25, 2019	10:00p	Portland Trail Blazers		Sacramento Kings
Fri, Oct 25, 2019	10:30p	Utah Jazz		Los Angeles Lakers
Sat, Oct 26, 2019	5:00p	Miami Heat		Milwaukee Bucks
Sat, Oct 26, 2019	7:00p	Philadelphia 76ers		Detroit Pistons
Sat, Oct 26, 2019	7:30p	Orlando Magic		Atlanta Hawks
Sat, Oct 26, 2019	7:30p	Boston Celtics		New York Knicks
Sat, Oct 26, 2019	8:00p	New Orleans Pelicans		Houston Rockets
Sat, Oct 26, 2019	8:00p	Indiana Pacers		Cleveland Cavaliers
Sat, Oct 26, 2019	8:00p	Toronto Raptors		Chicago Bulls
Sat, Oct 26, 2019	8:30p	Washington Wizards		San Antonio Spurs
Sat, Oct 26, 2019	9:00p	Sacramento Kings		Utah Jazz
Sat, Oct 26, 2019	10:00p	Los Angeles Clippers		Phoenix Suns

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[sandy666]

you can try PowerQuery aka Get&Transform,

eg.

Select
Thu, Oct 24, 2019
Select	Time	Team1	Team2
Thu, Oct 24, 2019	7:00p	Atlanta Hawks	Detroit Pistons
Thu, Oct 24, 2019	8:00p	Milwaukee Bucks	Houston Rockets
Thu, Oct 24, 2019	10:30p	Los Angeles Clippers	Golden State Warriors

 

[Grunter31]

Thanks, Unfortunately unless it's with a normal excel formula I can't do it.

 

[sandy666]

so maybe someone else will give you formula solution

have a nice day

 

[Grunter31]

=INDEX($C2:$C1499,MATCH($K$1,IF($A2:$A1499=$K$1,$A2:$A1499),0))

this is formula I'm using. It half works but when I change dates I get duplicates in the first few cells.

Cleveland Cavaliers

Cleveland Cavaliers

Cleveland Cavaliers

Detroit Pistons

Chicago Bulls

Memphis Grizzlies

Minnesota Timberwolves

Boston Celtics

Washington Wizards

New York Knicks

Oklahoma City Thunder

Denver Nuggets

Sacramento Kings

<colgroup><col></colgroup><tbody>
</tbody>

 

[sandy666]

maybe I misunderstood, It MUST be a formula?

 

[Grunter31]

Yes. Something like what I have. I'm missing something in my formula but just can't workout what.

 

[sandy666]

ok, I was just making sure

 

[AhoyNC]

Maybe something like this?
You will need Excel ver. 2010 or later with the AGGREGATE function.

Excel Workbook
	A	B	C	D	E	J	K	L
1	Date		Team A		Team B		Thu, Oct 24, 2019
2	Tue, Oct 22, 2019	8:00p	New Orleans Pelicans		Toronto Raptors		Atlanta Hawks	Detroit Pistons
3	Tue, Oct 22, 2019	10:30p	Los Angeles Lakers		Los Angeles Clippers		Milwaukee Bucks	Houston Rockets
4	Wed, Oct 23, 2019	7:00p	Cleveland Cavaliers		Orlando Magic		Los Angeles Clippers	Golden State Warriors
5	Wed, Oct 23, 2019	7:00p	Detroit Pistons		Indiana Pacers
6	Wed, Oct 23, 2019	7:00p	Chicago Bulls		Charlotte Hornets
7	Wed, Oct 23, 2019	7:30p	Memphis Grizzlies		Miami Heat
8	Wed, Oct 23, 2019	7:30p	Minnesota Timberwolves		Brooklyn Nets
9	Wed, Oct 23, 2019	7:30p	Boston Celtics		Philadelphia 76ers
10	Wed, Oct 23, 2019	8:30p	Washington Wizards		Dallas Mavericks
11	Wed, Oct 23, 2019	8:30p	New York Knicks		San Antonio Spurs
12	Wed, Oct 23, 2019	9:00p	Oklahoma City Thunder		Utah Jazz
13	Wed, Oct 23, 2019	10:00p	Denver Nuggets		Portland Trail Blazers
14	Wed, Oct 23, 2019	10:00p	Sacramento Kings		Phoenix Suns
15	Thu, Oct 24, 2019	7:00p	Atlanta Hawks		Detroit Pistons
16	Thu, Oct 24, 2019	8:00p	Milwaukee Bucks		Houston Rockets
17	Thu, Oct 24, 2019	10:30p	Los Angeles Clippers		Golden State Warriors
18	Fri, Oct 25, 2019	7:00p	Toronto Raptors		Boston Celtics
19	Fri, Oct 25, 2019	7:00p	Minnesota Timberwolves		Charlotte Hornets
20	Fri, Oct 25, 2019	7:30p	New York Knicks		Brooklyn Nets
21	Fri, Oct 25, 2019	8:00p	Washington Wizards		Oklahoma City Thunder
22	Fri, Oct 25, 2019	8:00p	Dallas Mavericks		New Orleans Pelicans
23	Fri, Oct 25, 2019	8:00p	Chicago Bulls		Memphis Grizzlies
24	Fri, Oct 25, 2019	9:00p	Phoenix Suns		Denver Nuggets
25	Fri, Oct 25, 2019	10:00p	Portland Trail Blazers		Sacramento Kings
26	Fri, Oct 25, 2019	10:30p	Utah Jazz		Los Angeles Lakers
27	Sat, Oct 26, 2019	5:00p	Miami Heat		Milwaukee Bucks
28	Sat, Oct 26, 2019	7:00p	Philadelphia 76ers		Detroit Pistons
29	Sat, Oct 26, 2019	7:30p	Orlando Magic		Atlanta Hawks
30	Sat, Oct 26, 2019	7:30p	Boston Celtics		New York Knicks
31	Sat, Oct 26, 2019	8:00p	New Orleans Pelicans		Houston Rockets
32	Sat, Oct 26, 2019	8:00p	Indiana Pacers		Cleveland Cavaliers
33	Sat, Oct 26, 2019	8:00p	Toronto Raptors		Chicago Bulls
34	Sat, Oct 26, 2019	8:30p	Washington Wizards		San Antonio Spurs
35	Sat, Oct 26, 2019	9:00p	Sacramento Kings		Utah Jazz
36	Sat, Oct 26, 2019	10:00p	Los Angeles Clippers		Phoenix Suns
Sheet

 

[Grunter31]

Thank you so much. Works 100% perfectly.

Been stuck for last 48 hours.

Once again Thank You