Football Fixture Results (How to find the last 5 fixtures involving a team)

Hey guys,

Basically I have a table with fixtures and results. I need to be able to find the last 5 matches involving a team whether it be home or away. The column headers are below.

Please Login or Register  to view this content.

HFound and AFound record how many times a team has appeared at home or away. This is being used for another formula which uses Sumproduct to get the results for the last five home games for the home team involved.

Is there a way I can use sumproduct to find the last 5 fixtures the team is involved in?

Suppose you have 380 fixtures (20 teams), then the last fixture should be on row 381. Using column I as a helper column, you could put this formula in I2:

=IF(F2="","",B2&"_"&COUNTIF(B2:B$381,B2))

which can then be copied down to the bottom of your fixture list. It will give a decreasing count of the games played by the home team, assuming that the result field is blank until a match is played. To get the rows of the last 5 matches for teamA, then, you just need a MATCH function to look for teamA_1, teamA_2 teamA_3, teamA_4 and teamA_5 in column I, and then you can use those rows in an INDEX function to return whichever parameters you need.

Hope this helps.

Pete

I dont have all the fixtures typed out. The table is being filled in as fixtures happen.

Well, the formula is still valid - just make sure that you have $381 in the COUNTIF function if you have 20 teams.

Note that the number is derived from 20 * 19, i.e. number of teams * (number of teams - 1) and then add one for your header row.

If you are still confused, then post an example workbook - the FAQ describes how to.

Hope this helps.

Pete

Originally Posted by daddylonglegs

You say "find" - what does that mean exactly in this context - do you want a calculation involving those last 5 fixtures or an actual list of them.......or something else?

Are the fixtures listed in date order (so the last 5 will be the bottom 5 positionally?)

I want to calculate how many times a team has won the game in their last 5 fixtures.

Will give Petes idea a go now.

Petes method gets me the result (H, D, A). However now I have the problem of finding out if the Team was home or away in the fixture to be able to see if the result was a win for the home team or the away team?

I thought you were looking for the last 5 home results, so the formula only looks at column B (home team) and gives a count for those fixtures for the home team. You could have a similar formula in column J which looks at the away team. Often when you look at some fixtures it will show:

TeamA (WWDWL) vs TeamB (LDWLW)

where the last 5 results are for home games only or away games only.

Hope this helps.

Pete