Extract Team Pairs from Table

Steve 1962

Active Member
Joined
Jan 3, 2006
Messages
379
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFG
1WeekHomeAwayWeekHomeAway
21Team 1Team 22Team 17Team 18
31Team 3Team 42Team 19Team 20
41Team 5Team 62Team 21Team 22
51Team 7Team 82Team 23Team 24
61Team 9Team 102Team 25Team 26
71Team 11Team 122Team 27Team 28
81Team 13Team 142Team 29Team 30
91Team 15Team 162Team 31Team 32
102Team 17Team 18
112Team 19Team 20
122Team 21Team 22
132Team 23Team 24
142Team 25Team 26
152Team 27Team 28
162Team 29Team 30
172Team 31Team 32
183Team 33Team 34
193Team 35Team 36
203Team 37Team 38
213Team 39Team 40
223Team 41Team 42
233Team 43Team 44
243Team 45Team 46
253Team 47Team 48
264Team 49Team 50
274Team 51Team 52
284Team 53Team 54
294Team 55Team 56
304Team 57Team 58
314Team 59Team 60
324Team 61Team 62
334Team 63Team 64
Sheet4
Cell Formulas
RangeFormula
E3:E9E3=E2
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
MrExcelPlayground22.xlsx
ABCDEFG
1WeekHomeAwayWeekHomeAway
21Team 1Team 222Team 17Team 18
31Team 3Team 42Team 19Team 20
41Team 5Team 62Team 21Team 22
51Team 7Team 82Team 23Team 24
61Team 9Team 102Team 25Team 26
71Team 11Team 122Team 27Team 28
81Team 13Team 142Team 29Team 30
91Team 15Team 162Team 31Team 32
102Team 17Team 18
112Team 19Team 20
122Team 21Team 22
132Team 23Team 24
142Team 25Team 26
152Team 27Team 28
162Team 29Team 30
172Team 31Team 32
183Team 33Team 34
193Team 35Team 36
203Team 37Team 38
213Team 39Team 40
223Team 41Team 42
233Team 43Team 44
243Team 45Team 46
253Team 47Team 48
264Team 49Team 50
274Team 51Team 52
284Team 53Team 54
294Team 55Team 56
304Team 57Team 58
314Team 59Team 60
324Team 61Team 62
334Team 63Team 64
Sheet21
Cell Formulas
RangeFormula
E2:G9E2=FILTER(A2:C33,A2:A33=D2)
Dynamic array formulas.
 
Upvote 0
Solution
Hey James

Works really well. Thanks very much for your help.

Have a good week.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

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