Find row in table which contains specific data

W

wbtczn

New Member
Joined
Aug 31, 2011
Messages
28
Office Version
  1. 2016
Platform
  1. Windows
I don't know if I'm just too tired or what, but my brain isn't working this out. I thought it was an INDEX MATCH situation, but now I don't know. Help?

What I need to have happen is to determine which week a specific matchup occurs. Assuming the first column is week and the next two column are matchups (in reality, there will be five columns of matchups) how would I get Excel to tell me that D3M1 is week 6?

1 D1D2 D3D4
2 M1D1 D2D3
3 D1D3 M2D5
4 M2D1 D5D3
5 D1D4 D3D2
6 M3D1 M4D3
7 D1D5 D3M1
8 M4D1 M5D3
9 D1M1 D3M2
10 M5D1 M1D3
11 D1M2 D3M3
12 D2D1 D4D3
13 D1M3 D3M4
 
Sort by date Sort by votes
Per your example wouldn't D3M1 be week 7?

See if this works for you.
This assumes you don't have duplicates in your match up columns.
This is an array formula and must be entered with CTRL-SHIFT-ENTER.
Excel Workbook
ABCDE
1WeekMUMUFind
21D1D2D3D4D3M1
32M1D1D2D3
43D1D3M2D57
54M2D1D5D3
65D1D4D3D2
76M3D1M4D3
87D1D5D3M1
98M4D1M5D3
109D1M1D3M2
1110M5D1M1D3
1211D1M2D3M3
1312D2D1D4D3
1413D1M3D3M4
15
Sheet



 
Upvote 0
Not really sure I underatnd your question here - where do you get D3M1 from, of is that just a code you need to find teh weeknum for?
[Table="width:, class:grid"][tr][td] [/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][/tr]
[tr][td]
1​
[/td][td]
1​
[/td][td]D1D2[/td][td]D3D4[/td][td]
7​
[/td][/tr]

[tr][td]
2​
[/td][td]
2​
[/td][td]M1D1[/td][td]D2D3[/td][td][/td][/tr]

[tr][td]
3​
[/td][td]
3​
[/td][td]D1D3[/td][td]M2D5[/td][td][/td][/tr]

[tr][td]
4​
[/td][td]
4​
[/td][td]M2D1[/td][td]D5D3[/td][td][/td][/tr]

[tr][td]
5​
[/td][td]
5​
[/td][td]D1D4[/td][td]D3D2[/td][td][/td][/tr]

[tr][td]
6​
[/td][td]
6​
[/td][td]M3D1[/td][td]M4D3[/td][td][/td][/tr]

[tr][td]
7​
[/td][td]
7​
[/td][td]D1D5[/td][td]D3M1[/td][td][/td][/tr]

[tr][td]
8​
[/td][td]
8​
[/td][td]M4D1[/td][td]M5D3[/td][td][/td][/tr]

[tr][td]
9​
[/td][td]
9​
[/td][td]D1M1[/td][td]D3M2[/td][td][/td][/tr]

[tr][td]
10​
[/td][td]
10​
[/td][td]M5D1[/td][td]M1D3[/td][td][/td][/tr]

[tr][td]
11​
[/td][td]
11​
[/td][td]D1M2[/td][td]D3M3[/td][td][/td][/tr]

[tr][td]
12​
[/td][td]
12​
[/td][td]D2D1[/td][td]D4D3[/td][td][/td][/tr]

[tr][td]
13​
[/td][td]
13​
[/td][td]D1M3[/td][td]D3M4[/td][td][/td][/tr]
[/table]
D1=INDEX($A$1:$A$13,IFERROR(MATCH("D3M1",$B$1:$B$13,0),MATCH("D3M1",$C$1:$C$13,0)))
 
Upvote 0
If you are using Excel 2010 or later, here is another option to return the first week when the matchup occurs.

In your sample data, each matchup only occurs once. Is that always the case?
If not, exactly what do you want returned?

Excel Workbook
ABCDEFGH
1WeekMUMUFind
21D1D2D3D4D3M1
32M1D1D2D3
43D1D3M2D5Week
54M2D1D5D37
65D1D4D3D2
76M3D1M4D3
87D1D5D3M1
98M4D1M5D3
109D1M1D3M2
1110M5D1M1D3
1211D1M2D3M3
1312D2D1D4D3
1413D1M3D3M4
15
Which week
 
Upvote 0
Sorry I didn't go into more detail. Here's what is happening. I am putting together a schedule for 10 teams who are split into 2 divisions (D - D or M). That's where the D1, D2, D3, D4, D5, M1, M2, M3, M4, and M5 come from. Each week, there are 5 games and over 18 weeks each team should play the other ones twice -- once as the away team and once as the home team. So, D3M1 means Division D Team 3 is at Division M team 1.

What I'm trying to do is as I put the schedule together is have Excel show me what matchups are created and what ones are still needed. So, as I create a matchup of D3 at M1, those two get concatenated into this table:



G1 G2 G3 G4 G5
1 D1D2 D3D4
2 M1D1 D2D3
3 D1D3 M2D5
4 M2D1 D5D3
5 D1D4 D3D2
6 M3D1 M4D3
7 D1D5 D3M1
8 M4D1 M5D3
9 D1M1 D3M2
10 M5D1 M1D3
11 D1M2 D3M3
12 D2D1 D4D3
13 D1M3 D3M4
14 D3D1
15 D1M4 D3M5
16 D4D1 M3D3
17 D1M5
18 D5D1


That data is then pulled into this table (using the formula I'm asking about) to show me where the matchups are filled and what is still needed:

Home
D1 D2 D3 D4 D5 M1 M2 M3 M4 M5
Away D1 1 1 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A
D2 #N/A 1 1 1 1 1 1 1 1 1
D3 #N/A #N/A 1 #VALUE! 1 #N/A #N/A #N/A #N/A #N/A
D4 #N/A 1 #N/A 1 1 1 1 1 1 1
D5 #N/A 1 #N/A 1 1 1 1 1 1 1
M1 #VALUE! 1 #N/A 1 1 1 1 1 1 1
M2 #N/A 1 1 1 #N/A 1 1 1 1 1
M3 #N/A 1 #N/A 1 1 1 1 1 1 1
M4 #N/A 1 #N/A 1 1 1 1 1 1 1
M5 #N/A 1 #N/A 1 1 1 1 1 1 1


The stats in the second table are based upon the formula AhoyNC recommended. It looks like if the combination isn't found yet that the result is a 1. It should be a 0 or a blank.

Also....how are you pasting in nicely formatted tables in the thread?
 
Upvote 0
wbtczn said:
Also....how are you pasting in nicely formatted tables in the thread?
Click to expand...
Read my signature block below.
It will be easier if we can see the data and layout better. :)
 
Upvote 0
Peter_SSs said:
Read my signature block below.
It will be easier if we can see the data and layout better. :)
Click to expand...

Thanks Peter! Here's the two sections:


Excel 2012
ACADAEAFAGAH
1G1G2G3G4G5
21D1D2D3D4
32M1D1D2D3
43D1D3M2D5
54M2D1D5D3
65D1D4D3D2
76M3D1M4D3
87D1D5D3M1
98M4D1M5D3
109D1M1D3M2
1110M5D1M1D3
1211D1M2D3M3
1312D2D1D4D3
1413D1M3D3M4
1514D3D1
1615D1M4D3M5
1716D4D1M3D3
1817D1M5
1918D5D1
Schedule



Excel 2012
ABCDEFGHIJKL
25Home
26D1D2D3D4D5M1M2M3M4M5
27AwayD111################################
28D2####111111111
29D3########1####1####################
30D4####1####1111111
31D5####1####1111111
32M1####1####1111111
33M2####111####11111
34M3####1####1111111
35M4####1####1111111
36M5####1####1111111
Schedule
 
Upvote 0
The SUMPRODUCT formula in cell E4 will return a 0 if not found.

Is your table to show the week number. If so maybe the example below will work.
Just copy the formula in C19 down and across.
Excel Workbook
ABCDEFGHIJKLM
1WeekMUMUFind
21D1D2D3D4D3M1
32M1D1D2D3
43D1D3M2D57
54M2D1D5D3
65D1D4D3D2
76M3D1M4D3
87D1D5D3M1
98M4D1M5D3
109D1M1D3M2
1110M5D1M1D3
1211D1M2D3M3
1312D2D1D4D3
1413D1M3D3M4
15
16
17Home
18D1D2D3D4D5M1M2M3M4M5
19AwayD1012000246810
20D21050000000
21D3320124100068
22D45010000000
23D57000003000
24M19070000000
25M211090000000
26M3130110000000
27M400130000000
28M50000000000
29
Sheet
 
Upvote 0
Comments:
- If you happen to make a mistake and schedule the same matchup twice, the SUMPRODUCT formula suggested will return an incorrect result, see cell D43 below. Of course, that is easily fixed with a count check similar to the one I have used in the first result table below.

- Also, if you happen to change the values in column AC from numeriacl to, say, W1, W2, W3 etc the SUMPRODUCT will fail.

- The SUMPRODUCT formula is considerably slower to calculate than the AGGREGATE one.


Having said all that I'm not suggesting that you don't use SUMPRODUCT if it suits, as it is simpler and works in all Excel versions whereas AGGREGATE requires Excel 2010 or later.

Anyway, for completeness, here is my updated version too.
Both C27 and C41 are copied across and down.

Excel Workbook
ACADAEAFAGAH
1G1G2G3G4G5
21D1D2D3D4
32M1D1D2D3
43D1D3M2D5
54M2D1D5D3
65D1D4D3D2D2D3
76M3D1M4D3
87D1D5D3M1
98M4D1M5D3
109D1M1D3M2
1110M5D1M1D3
1211D1M2D3M3
1312D2D1D4D3
1413D1M3D3M4
1514D3D1
1615D1M4D3M5
1716D4D1M3D3
1817D1M5
1918D5D1
Schedule




Excel Workbook
ABCDEFGHIJKLM
24
25Home
26D1D2D3D4D5M1M2M3M4M5
27AwayD1 12141618246810
28D215
29D33mult124101668
30D451
31D573
32M197
33M2119
34M31311
35M41513
36M51715
37
38
39Home
40D1D2D3D4D5M1M2M3M4M5
41AwayD1012141618246810
42D21050000000
43D33701241001668
44D45010000000
45D57000003000
46M19070000000
47M211090000000
48M3130110000000
49M4150130000000
50M5170150000000
51
Schedule
 
Upvote 0
I think I'm making this too hard on myself. This first chart is where I'm starting and where the "D1M1", etc., was coming from. Perhaps I can take that data to load into the second chart to show which week the matchups are occurring. The whole reason I'm trying to do this is to make sure that only one occurrence of each matchup occurs. Maybe I use a COUNTIF function in the second chart....


Excel 2012
BCDEFGHIJKL
1weekVHVHVHVHVH
21D1D2D3D4D5M5M1M2M3M4
32M4D1D2D3D4D5M5M3M2M1
43D1D3M2D5
54M2D1D5D3
65D1D4D3D2
76M3D1M4D3
87D1D5D3M1
98M4D1M5D3
109D1M1D3M2
1110M5D1M1D3
1211D1M2D3M3
1312D2D1D4D3
1413D1M3D3M4
1514D3D1
1615D1M4D3M5
1716D4D1M3D3
1817D1M5
1918D5D1
Schedule



Excel 2012
ABCDEFGHIJKL
25Home
26D1D2D3D4D5M1M2M3M4M5
27VisitorD111################################
28D2####111111111
29D3########1####1####################
30D4####1####1####11111
31D5####1####111111####
32M111####1111111
33M2####111########1111
34M3####1####11111####1
35M4####1####1111111
36M5####1####1111####11
Schedule


Also, I'm not sure why it says "Excel 2012", I'm using Excel 2013.
 
Upvote 0
You must log in or register to reply here.

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