Help on formula to find last occurrence of values in a list to produce football form table

Hi,

I've been working on creating a detailed spreadsheet to analyse football matches and leagues. Along the way I've managed to create a lot of detailed statistics and learnt a lot form these forums, but I've now come across something that has completely defeated me... and so hoping for some help.

I've attached an example and in this case I have:

Column A: Fixture Date
Column B: Home Team
Column C: Away Team
Column D: Home Team Goals
Column E: Away Team Goals

This particular sheet has over 4000 matches dating back to 2004, and I would like to create a list of a team's (e.g. Arsenal in this particular example) last 10 fixtures and results (i.e. to look at recent form). I've created an example of how this would look on the same worksheet.

Essentially I've put Arsenal in cell I2 and manually entered some details below that cell to give an example of how I would like it to appear... simply the last 10 fixtures they played home or away listed by date / home team / away team / home goals / away goals (i.e. exactly as the main data appears but just for one specific team's last 10 games).

I've been trying various combinations of INDEX / MATCH / SUMPRODUCT / LARGE etc. but not getting anywhere. Any help would be much appreciated and potentially save me from going insane! Thanks!

sourabhg98's formula is a different approach than I was taking (and probably more of what you were looking for). The only issue I see is sourabhg98's solution doesn't appear to discriminate against games that haven't been played yet. I am guessing that this could be easily be fixed with an "if" statement. I was going with a less elegant method of using an advanced filter which involves a little more work by the user, but the upside is it should allow for more versatile analysis of the data set.

Haha... you guys are too quick for me, managed to get 3 solutions done in the time it's taken me to try and wrap my head around the first one. Thank you so much to you both... what a great forum.

Ultimately Sourabhg98's second version is perfect for what I need (and also closer to what I was originally trying to do myself) but greatly appreciate the efforts and quick response from both of you.

No problem! Actually now I have this sorted it's given me one other idea.

If in column G I added in a season yaer (e.g. 2004, 2005, 2006, 2007 etc.) what would I need to add to the formula to make sure it only returned results from that season.

E.g. if a new season starts and Arsenal have only played 5 games, I would only want it to return those games and not the last 5 from the previous season.

Thanks