Hi I was wondering if you could help me with a formula... I need to look up a value in a range of cells, and return data based on the newest entries (based on a date). I have attached the spreadsheet.. I will go into more detail now.
The Data
The 'games' tab has 5 columns of data.. Home team, away team, Date, FTHG, FTAG, TG.... In this sheet we have all of the games played in the English premier league. As the season goes on, this worksheet will grow every week. At the end of the season this worksheet will be hundreds of lines long.
The requirement
In the 'last 2 games' worksheet i need to find a way to show all of this data for the last 2 games that the Home team has played. You can see that in this worksheet i have keyed in Arsenal and Liverpool. this is because i know both of these teams have played more than 2 home games.
The 2 latest dates Arsenal played at home and the required data is
Arsenal Southampton 15/09/2012 6 1 7
Arsenal Chelsea 29/09/2012 1 2 3
In the last 2 games tab, I imagine i will need formulas in cells B2, C2, D2, E2 in order to return the first row of data.
Here comes the curve ball...... as the 'games' tab will be continually growing, the formula will need to always return the 2 newest entries based on the game dates.
For example, if Arsenal played another home games on 10/10/2012, the game against Southampton on 15/09/2012 would no longer be returned in the 'last 2 games tab'.
Any help on this would be greatly appreciated(as always)
Bookmarks