Index, Match when values the same

G

Grunter31

Board Regular
Joined
Mar 11, 2017
Messages
53
Office Version
  1. 365
Platform
  1. Windows
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.

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

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
you can try PowerQuery aka Get&Transform,

eg.

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Select[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#FFFF00]Thu, Oct 24, 2019[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Select[/td][td=bgcolor:#70AD47]Time[/td][td=bgcolor:#70AD47]Team1[/td][td=bgcolor:#70AD47]Team2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Thu, Oct 24, 2019[/td][td=bgcolor:#E2EFDA]7:00p[/td][td=bgcolor:#E2EFDA]Atlanta Hawks[/td][td=bgcolor:#E2EFDA]Detroit Pistons[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Thu, Oct 24, 2019[/td][td]8:00p[/td][td]Milwaukee Bucks[/td][td]Houston Rockets[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Thu, Oct 24, 2019[/td][td=bgcolor:#E2EFDA]10:30p[/td][td=bgcolor:#E2EFDA]Los Angeles Clippers[/td][td=bgcolor:#E2EFDA]Golden State Warriors[/td][/tr]
[/table]
 
Upvote 0
Thanks, Unfortunately unless it's with a normal excel formula I can't do it.
 
Upvote 0
=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.

[TABLE="width: 128"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Cleveland Cavaliers[/TD]
[/TR]
[TR]
[TD]Cleveland Cavaliers[/TD]
[/TR]
[TR]
[TD]Cleveland Cavaliers[/TD]
[/TR]
[TR]
[TD]Detroit Pistons[/TD]
[/TR]
[TR]
[TD]Chicago Bulls[/TD]
[/TR]
[TR]
[TD]Memphis Grizzlies[/TD]
[/TR]
[TR]
[TD]Minnesota Timberwolves[/TD]
[/TR]
[TR]
[TD]Boston Celtics[/TD]
[/TR]
[TR]
[TD]Washington Wizards[/TD]
[/TR]
[TR]
[TD]New York Knicks[/TD]
[/TR]
[TR]
[TD]Oklahoma City Thunder[/TD]
[/TR]
[TR]
[TD]Denver Nuggets[/TD]
[/TR]
[TR]
[TD]Sacramento Kings[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Yes. Something like what I have. I'm missing something in my formula but just can't workout what.
 
Upvote 0
Maybe something like this?
You will need Excel ver. 2010 or later with the AGGREGATE function.
Excel Workbook
ABCDEJKL
1DateTeam ATeam BThu, Oct 24, 2019
2Tue, Oct 22, 20198:00pNew Orleans PelicansToronto RaptorsAtlanta HawksDetroit Pistons
3Tue, Oct 22, 201910:30pLos Angeles LakersLos Angeles ClippersMilwaukee BucksHouston Rockets
4Wed, Oct 23, 20197:00pCleveland CavaliersOrlando MagicLos Angeles ClippersGolden State Warriors
5Wed, Oct 23, 20197:00pDetroit PistonsIndiana Pacers
6Wed, Oct 23, 20197:00pChicago BullsCharlotte Hornets
7Wed, Oct 23, 20197:30pMemphis GrizzliesMiami Heat
8Wed, Oct 23, 20197:30pMinnesota TimberwolvesBrooklyn Nets
9Wed, Oct 23, 20197:30pBoston CelticsPhiladelphia 76ers
10Wed, Oct 23, 20198:30pWashington WizardsDallas Mavericks
11Wed, Oct 23, 20198:30pNew York KnicksSan Antonio Spurs
12Wed, Oct 23, 20199:00pOklahoma City ThunderUtah Jazz
13Wed, Oct 23, 201910:00pDenver NuggetsPortland Trail Blazers
14Wed, Oct 23, 201910:00pSacramento KingsPhoenix Suns
15Thu, Oct 24, 20197:00pAtlanta HawksDetroit Pistons
16Thu, Oct 24, 20198:00pMilwaukee BucksHouston Rockets
17Thu, Oct 24, 201910:30pLos Angeles ClippersGolden State Warriors
18Fri, Oct 25, 20197:00pToronto RaptorsBoston Celtics
19Fri, Oct 25, 20197:00pMinnesota TimberwolvesCharlotte Hornets
20Fri, Oct 25, 20197:30pNew York KnicksBrooklyn Nets
21Fri, Oct 25, 20198:00pWashington WizardsOklahoma City Thunder
22Fri, Oct 25, 20198:00pDallas MavericksNew Orleans Pelicans
23Fri, Oct 25, 20198:00pChicago BullsMemphis Grizzlies
24Fri, Oct 25, 20199:00pPhoenix SunsDenver Nuggets
25Fri, Oct 25, 201910:00pPortland Trail BlazersSacramento Kings
26Fri, Oct 25, 201910:30pUtah JazzLos Angeles Lakers
27Sat, Oct 26, 20195:00pMiami HeatMilwaukee Bucks
28Sat, Oct 26, 20197:00pPhiladelphia 76ersDetroit Pistons
29Sat, Oct 26, 20197:30pOrlando MagicAtlanta Hawks
30Sat, Oct 26, 20197:30pBoston CelticsNew York Knicks
31Sat, Oct 26, 20198:00pNew Orleans PelicansHouston Rockets
32Sat, Oct 26, 20198:00pIndiana PacersCleveland Cavaliers
33Sat, Oct 26, 20198:00pToronto RaptorsChicago Bulls
34Sat, Oct 26, 20198:30pWashington WizardsSan Antonio Spurs
35Sat, Oct 26, 20199:00pSacramento KingsUtah Jazz
36Sat, Oct 26, 201910:00pLos Angeles ClippersPhoenix Suns
Sheet
 
Upvote 0
Thank you so much. Works 100% perfectly.

Been stuck for last 48 hours.

Once again Thank You
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Index Match function List based on date
Replies
7
Views
240
Fluff
Fluff
G
  • Solved
Combobox search dropdown triggering when editing other sheets
2
Replies
10
Views
818
Akuini
Akuini
gtd526
  • Question Question
Conditional Formatting for Matching Odd Rows only
Replies
4
Views
250
gtd526
gtd526
K
  • Question Question
Dynamic Address for Use in Function Always returns ERROR
Replies
3
Views
513
Eric W
Eric W
G
  • Solved
Sort/filter formula used for search formula combobox issue with shorter named values, Formula help for search combobox
Replies
6
Views
821
Akuini
Akuini

Forum statistics

Threads
1,223,106
Messages
6,170,130
Members
452,304
Latest member
Thelingly95

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