Displaying Last X Results for Soccer League Table

[Statto]

For a soccer league table I'm building, I'm trying to add a few columns that display each team's form in the last few matches. So, it will state 'W', 'D' or 'L' for a certain number of recent matches. Like this for each team in the league table: WWDLW.

In the attachment example, I've pretty much got the concept working, having used the invaluable INDEX/MATCH/LARGE functions and a serial date column. The problem is, at the moment the DDW you see at the bottom is taking the whole table into consideration. I'm trying to get it so only Arsenal's recent results are taken into account. I feel like this shouldn't be too difficult, but after numerous attempts of using different functions I cannot get it to work.

Any help appreciated.

Book1.xlsx

[Richard Buttrey]

Hi,

What result are you expecting?
DWL
being the last 3 home results for Arsenal.

But what about if the team also features in the Away team in one of the last 3 matches. And does the definition of 'recent' vary or is it always 3.

[micope21]

Hi

See the file!

Regard
micope21

[Statto]

Hi

See the file!

Regard
micope21

Thanks, that works really well. I had a little trouble understanding the structure of it at first but it became clearer. The only problem with it is that it only works properly when the games are in a specific order. It doesn't take the date into consideration. Eg. you can have a game played in 2010 at the bottom of the table and all the others in 2014, yet the 2010 game will display as the most recent game, just because it's at the bottom. So the next step is to try and get it to consider the date.

[micope21]

Hi

You can now change start date and End date!!
See the file!

Regard
micope21

[Statto]

Hi

You can now change start date and End date!!
See the file!

Regard
micope21

Thanks again for your efforts. I still have an issue with the fact that game outcomes still are not ordered by their date when the eg. W L D is displayed.

Is it possible for a simple adaption to be made to the spreadsheet I uploaded? This does order the match outcomes in accordance to their date, but it DOESN'T take into consideration a specific club.

I'm very sure this is a pretty easy thing to integrate — most likely another INDEX/MATCH function that can look for games played by a specific club.

Book1.xlsx

[micope21]

Hi

See the file!
2 choice in the file!
I hope this had solved your problem?

This is your first time excel forum?
Please click on Add Reputation for doing a job for you!
If you are happy with a solution to your problem?
Click Thread Tools above your first post,
select "Mark your thread as Solved".

Regard
micope21

[Statto]

Hi

See the file!
2 choice in the file!
I hope this had solved your problem?

This is your first time excel forum?
Please click on Add Reputation for doing a job for you!
If you are happy with a solution to your problem?
Click Thread Tools above your first post,
select "Mark your thread as Solved".

Regard
micope21

You're getting closer. Still the issue remains where the date is not being taken into consideration. I've traced the dependants for both the date and serial date columns — there are none. Why is this? In the workbook I attached I included these and it worked. All I need added to what I attached is for the spreadsheet to take into consideration the club that is specified in the lookup cell.

Once again, what I included is an example. You ask if I need 3, 5 or 10 of the most recent games and whether I need home, away or both included. I don't need that, I can do that myself. I just need the system to take into consideration the DATE and the CLUB that is specified in the cell. At the moment, you're spreadsheet you attached only considers the CLUB, not the DATE as well.

Do you understand?

[micope21]

Hi

See the file!!

Regard
micope21

[Statto]

That works well. Thanks!

[Statto]

Sorry for the double post. One more thing, I have used what you have given me and adapted it for my own needs. I am trying to avoid having an overall outcome column and only have a home and away. The problem is with the overall section. If you look at the attachment, I'm trying to find a way of displaying the overall outcomes (cells B17:F17) without having an overall column that states whether the lookup team has W, D or L. The reason being is when I have all of this in a league table, I don't want to have a giant table that lists whether all 20 teams have W, D or L against each other. That would an be inefficient method.

Book1.xlsx

[micope21]

Hi Statto

That the problem if you want each column HomeW/D/L and AwayW/D/L instead one column Home and Away point to Arsenal.

Too many argument in the formulas?

Set up Dummy sheet?

See the highlight yellow?

See the file!

Regard
micope21

[Statto]

Hi Statto

That the problem if you want each column HomeW/D/L and AwayW/D/L instead one column Home and Away point to Arsenal.

Too many argument in the formulas?

Set up Dummy sheet?

See the highlight yellow?

See the file!

Regard
micope21

Thanks, that works great! Still confused why there is an identical yellow table at the bottom when surely the formulas in the above table could just consider the cells in that?