# Season Fixtures extracted

#### jbloom17

##### New Member
Hi All,

The fixtures have been sent for the full league in the format below. I am trying to work a formula so that it will automatically extract the fixture for a set team and place it with the date on an additional tab.

 19-Sep-15 Old Loughts 1 : CoP 1 Wapping 1 : Chelmsford 1 St Albans 1 : Cambridge Uni 1 Ipswich 1 : Cambridge City 2 Havering 1 : Blueharts 1 Dereham 1 : Bedford 1 26-Sep-15 CoP 1 : Dereham 1 Chelmsford 1 : Old Loughts 1 Cambridge Uni 1 : Wapping 1 Cambridge City 2 : St Albans 1 Blueharts 1 : Ipswich 1 Bedford 1 : Havering 1

<tbody>
</tbody>

#### 123rickfear

##### Active Member
on the other tab are you searching for a fixture or a team's fixtures?

i.e on the other tab if you type in "Old Loughts 1 : CoP1" do you want it to return the date "19-Sep-15", or do you want to be able to type in a team name and return all the fixtures for that team?

#### jbloom17

##### New Member
Hi, thanks for trying to help..

im looking to type a team name and then it returns all fixtures for that team.

#### 123rickfear

##### Active Member
this is a tough one.

I can get the home games in one column, and the away games in another, but am still trying to get one list.

Let me know if this will do for now.

Rick

#### jbloom17

##### New Member
Hi Rick,

That could also work, thank you.

Takes away the manual filter, cut and paste.

Thank you so much for your help

#### 123rickfear

##### Active Member
in your original question is the home in team in column a and the away team in column c?

Yep, thanks

#### 123rickfear

##### Active Member
on the original sheet in D1 put =IF(ISNUMBER(A1),A1,"")

then in D2 put =IF(ISNUMBER(A2),A2,D1) and drag down.

in E2 put =TEXT(D2,"dd/mm/yyyy")&" - "&A2&" v "&C2 and drag down.

in sheet 2 in B1 put {=INDEX(Sheet1!\$E\$2:\$E\$50,SMALL(IF(Sheet2!\$A\$1=Sheet1!\$A\$2:\$A\$50,ROW(Sheet1!\$A\$2:\$A\$50)-ROW(Sheet1!\$A\$2)+1),ROW(1:1)))}

ensure you don't type copy the {}, you need to enter the formual then ctrl+shift+enter. then drag down, this should give you all your home games.

in C1 put {=INDEX(Sheet1!\$E\$2:\$E\$50,SMALL(IF(Sheet2!\$A\$1=Sheet1!\$C\$2:\$C\$50,ROW(Sheet1!\$C\$2:\$C\$50)-ROW(Sheet1!\$C\$2)+1),ROW(1:1)))}

again don't type/ copy the {}, then drag down.

you should now have a list of home games in column a and away games in column B.

Rick

#### jbloom17

##### New Member
Superstar Thank you

1,082,342
Messages
5,364,783
Members
400,815
Latest member
gangstar67

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...