Extract Team Pairs from Table

[Steve 1962]

Hi

I require formulas in cells F2:G9 that extract the team name from the table (A2:C33) dependent on the value in cell E2 which nominates the week of the table that I require both the HOME and AWAY team.

The table (A2:C33) will be static but in reality, may have up to 30 x weeks (only 4 in this example).

Thanks

Book1
	A	B	C	D	E	F	G
1	Week	Home	Away		Week	Home	Away
2	1	Team 1	Team 2		2	Team 17	Team 18
3	1	Team 3	Team 4		2	Team 19	Team 20
4	1	Team 5	Team 6		2	Team 21	Team 22
5	1	Team 7	Team 8		2	Team 23	Team 24
6	1	Team 9	Team 10		2	Team 25	Team 26
7	1	Team 11	Team 12		2	Team 27	Team 28
8	1	Team 13	Team 14		2	Team 29	Team 30
9	1	Team 15	Team 16		2	Team 31	Team 32
10	2	Team 17	Team 18
11	2	Team 19	Team 20
12	2	Team 21	Team 22
13	2	Team 23	Team 24
14	2	Team 25	Team 26
15	2	Team 27	Team 28
16	2	Team 29	Team 30
17	2	Team 31	Team 32
18	3	Team 33	Team 34
19	3	Team 35	Team 36
20	3	Team 37	Team 38
21	3	Team 39	Team 40
22	3	Team 41	Team 42
23	3	Team 43	Team 44
24	3	Team 45	Team 46
25	3	Team 47	Team 48
26	4	Team 49	Team 50
27	4	Team 51	Team 52
28	4	Team 53	Team 54
29	4	Team 55	Team 56
30	4	Team 57	Team 58
31	4	Team 59	Team 60
32	4	Team 61	Team 62
33	4	Team 63	Team 64
Sheet4

Cell Formulas
Range	Formula
E3:E9	E3	=E2

 

[JamesCanale]

MrExcelPlayground22.xlsx
	A	B	C	D	E	F	G
1	Week	Home	Away		Week	Home	Away
2	1	Team 1	Team 2	2	2	Team 17	Team 18
3	1	Team 3	Team 4		2	Team 19	Team 20
4	1	Team 5	Team 6		2	Team 21	Team 22
5	1	Team 7	Team 8		2	Team 23	Team 24
6	1	Team 9	Team 10		2	Team 25	Team 26
7	1	Team 11	Team 12		2	Team 27	Team 28
8	1	Team 13	Team 14		2	Team 29	Team 30
9	1	Team 15	Team 16		2	Team 31	Team 32
10	2	Team 17	Team 18
11	2	Team 19	Team 20
12	2	Team 21	Team 22
13	2	Team 23	Team 24
14	2	Team 25	Team 26
15	2	Team 27	Team 28
16	2	Team 29	Team 30
17	2	Team 31	Team 32
18	3	Team 33	Team 34
19	3	Team 35	Team 36
20	3	Team 37	Team 38
21	3	Team 39	Team 40
22	3	Team 41	Team 42
23	3	Team 43	Team 44
24	3	Team 45	Team 46
25	3	Team 47	Team 48
26	4	Team 49	Team 50
27	4	Team 51	Team 52
28	4	Team 53	Team 54
29	4	Team 55	Team 56
30	4	Team 57	Team 58
31	4	Team 59	Team 60
32	4	Team 61	Team 62
33	4	Team 63	Team 64
Sheet21

Cell Formulas
Range	Formula
E2:G9	E2	=FILTER(A2:C33,A2:A33=D2)
Dynamic array formulas.

 

[Steve 1962]

Hey James

Works really well. Thanks very much for your help.

Have a good week.