Find row in table which contains specific data

[wbtczn]

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

 

[AhoyNC]

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
	A	B	C	D	E
1	Week	MU	MU		Find
2	1	D1D2	D3D4		D3M1
3	2	M1D1	D2D3
4	3	D1D3	M2D5		7
5	4	M2D1	D5D3
6	5	D1D4	D3D2
7	6	M3D1	M4D3
8	7	D1D5	D3M1
9	8	M4D1	M5D3
10	9	D1M1	D3M2
11	10	M5D1	M1D3
12	11	D1M2	D3M3
13	12	D2D1	D4D3
14	13	D1M3	D3M4
15
Sheet

 

[FDibbins]

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?

	A​	B​	C​	D​
1​	1​	D1D2	D3D4	7​
2​	2​	M1D1	D2D3
3​	3​	D1D3	M2D5
4​	4​	M2D1	D5D3
5​	5​	D1D4	D3D2
6​	6​	M3D1	M4D3
7​	7​	D1D5	D3M1
8​	8​	M4D1	M5D3
9​	9​	D1M1	D3M2
10​	10​	M5D1	M1D3
11​	11​	D1M2	D3M3
12​	12​	D2D1	D4D3
13​	13​	D1M3	D3M4

D1=INDEX($A$1:$A$13,IFERROR(MATCH("D3M1",$B$1:$B$13,0),MATCH("D3M1",$C$1:$C$13,0)))

 

[Peter_SSs]

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
	A	B	C	D	E	F	G	H
1	Week	MU	MU					Find
2	1	D1D2	D3D4					D3M1
3	2	M1D1	D2D3
4	3	D1D3	M2D5					Week
5	4	M2D1	D5D3					7
6	5	D1D4	D3D2
7	6	M3D1	M4D3
8	7	D1D5	D3M1
9	8	M4D1	M5D3
10	9	D1M1	D3M2
11	10	M5D1	M1D3
12	11	D1M2	D3M3
13	12	D2D1	D4D3
14	13	D1M3	D3M4
15
Which week

 

[wbtczn]

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?

 

[Peter_SSs]

Also....how are you pasting in nicely formatted tables in the thread?

Read my signature block below.
It will be easier if we can see the data and layout better.

 

[wbtczn]

Read my signature block below.
It will be easier if we can see the data and layout better.

Thanks Peter! Here's the two sections:

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

Excel 2012
	A	B	C	D	E	F	G	H	I	J	K	L
25			Home
26			D1	D2	D3	D4	D5	M1	M2	M3	M4	M5
27	Away	D1	1	1	####	####	####	####	####	####	####	####
28		D2	####	1	1	1	1	1	1	1	1	1
29		D3	####	####	1	####	1	####	####	####	####	####
30		D4	####	1	####	1	1	1	1	1	1	1
31		D5	####	1	####	1	1	1	1	1	1	1
32		M1	####	1	####	1	1	1	1	1	1	1
33		M2	####	1	1	1	####	1	1	1	1	1
34		M3	####	1	####	1	1	1	1	1	1	1
35		M4	####	1	####	1	1	1	1	1	1	1
36		M5	####	1	####	1	1	1	1	1	1	1
Schedule

 

[AhoyNC]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Week	MU	MU		Find
2	1	D1D2	D3D4		D3M1
3	2	M1D1	D2D3
4	3	D1D3	M2D5		7
5	4	M2D1	D5D3
6	5	D1D4	D3D2
7	6	M3D1	M4D3
8	7	D1D5	D3M1
9	8	M4D1	M5D3
10	9	D1M1	D3M2
11	10	M5D1	M1D3
12	11	D1M2	D3M3
13	12	D2D1	D4D3
14	13	D1M3	D3M4
15
16
17			Home
18			D1	D2	D3	D4	D5	M1	M2	M3	M4	M5
19	Away	D1	0	12	0	0	0	2	4	6	8	10
20		D2	1	0	5	0	0	0	0	0	0	0
21		D3	3	2	0	12	4	10	0	0	6	8
22		D4	5	0	1	0	0	0	0	0	0	0
23		D5	7	0	0	0	0	0	3	0	0	0
24		M1	9	0	7	0	0	0	0	0	0	0
25		M2	11	0	9	0	0	0	0	0	0	0
26		M3	13	0	11	0	0	0	0	0	0	0
27		M4	0	0	13	0	0	0	0	0	0	0
28		M5	0	0	0	0	0	0	0	0	0	0
29
Sheet

 

[Peter_SSs]

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
	AC	AD	AE	AF	AG	AH
1		G1	G2	G3	G4	G5
2	1	D1D2	D3D4
3	2	M1D1	D2D3
4	3	D1D3	M2D5
5	4	M2D1	D5D3
6	5	D1D4	D3D2	D2D3
7	6	M3D1	M4D3
8	7	D1D5	D3M1
9	8	M4D1	M5D3
10	9	D1M1	D3M2
11	10	M5D1	M1D3
12	11	D1M2	D3M3
13	12	D2D1	D4D3
14	13	D1M3	D3M4
15	14	D3D1
16	15	D1M4	D3M5
17	16	D4D1	M3D3
18	17	D1M5
19	18	D5D1
Schedule

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
24
25			Home
26			D1	D2	D3	D4	D5	M1	M2	M3	M4	M5
27	Away	D1		12	14	16	18	2	4	6	8	10
28		D2	1		5
29		D3	3	mult		12	4	10		16	6	8
30		D4	5		1
31		D5	7						3
32		M1	9		7
33		M2	11		9
34		M3	13		11
35		M4	15		13
36		M5	17		15
37
38
39			Home
40			D1	D2	D3	D4	D5	M1	M2	M3	M4	M5
41	Away	D1	0	12	14	16	18	2	4	6	8	10
42		D2	1	0	5	0	0	0	0	0	0	0
43		D3	3	7	0	12	4	10	0	16	6	8
44		D4	5	0	1	0	0	0	0	0	0	0
45		D5	7	0	0	0	0	0	3	0	0	0
46		M1	9	0	7	0	0	0	0	0	0	0
47		M2	11	0	9	0	0	0	0	0	0	0
48		M3	13	0	11	0	0	0	0	0	0	0
49		M4	15	0	13	0	0	0	0	0	0	0
50		M5	17	0	15	0	0	0	0	0	0	0
51
Schedule

 

[wbtczn]

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
	B	C	D	E	F	G	H	I	J	K	L
1	week	V	H	V	H	V	H	V	H	V	H
2	1	D1	D2	D3	D4	D5	M5	M1	M2	M3	M4
3	2	M4	D1	D2	D3	D4	D5	M5	M3	M2	M1
4	3	D1	D3	M2	D5
5	4	M2	D1	D5	D3
6	5	D1	D4	D3	D2
7	6	M3	D1	M4	D3
8	7	D1	D5	D3	M1
9	8	M4	D1	M5	D3
10	9	D1	M1	D3	M2
11	10	M5	D1	M1	D3
12	11	D1	M2	D3	M3
13	12	D2	D1	D4	D3
14	13	D1	M3	D3	M4
15	14	D3	D1
16	15	D1	M4	D3	M5
17	16	D4	D1	M3	D3
18	17	D1	M5
19	18	D5	D1
Schedule

Excel 2012
	A	B	C	D	E	F	G	H	I	J	K	L
25			Home
26			D1	D2	D3	D4	D5	M1	M2	M3	M4	M5
27	Visitor	D1	1	1	####	####	####	####	####	####	####	####
28		D2	####	1	1	1	1	1	1	1	1	1
29		D3	####	####	1	####	1	####	####	####	####	####
30		D4	####	1	####	1	####	1	1	1	1	1
31		D5	####	1	####	1	1	1	1	1	1	####
32		M1	1	1	####	1	1	1	1	1	1	1
33		M2	####	1	1	1	####	####	1	1	1	1
34		M3	####	1	####	1	1	1	1	1	####	1
35		M4	####	1	####	1	1	1	1	1	1	1
36		M5	####	1	####	1	1	1	1	####	1	1
Schedule

Also, I'm not sure why it says "Excel 2012", I'm using Excel 2013.