Formula required to return 2 latest records in a date range

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)

Thats brilliant i have the formulas now so im almost there.....

going forward i need to be able to do this for every single team in the league.....can you tell me how you converted the games tab into a table please... that way i can try it for myself and see if i can get it all to work on my own.

appreciate the help

Originally Posted by Fotis1991

See if this helps you.

Edit: I haven't see benishiryo's(almost same) answer..

Morning Fotis... thanks for your help again

I have tried editing your sheet so that the 'last 3 games tab' has Everton in cells A6 and A7..... But when i copy the formula down, it cant seem to get it to work...

I have attatched the sheet... could you be so kind as to point out what i have done wrong. Hopefully i can then understand how it works and i can apply it for all teams.

Im really struggling with this....

I have taken your latest sheet, added Newcastle and Norwich and copied the formulas down.

The Newcastle lines are not returning the 2 latest results and the Norwich entries, are showing errors.The only differences i can see in the formulas are the ROW ... Some say B1 and some Say G.

I'm fairly good at understanding how a formula works when i see it..... but this one has me beat all ends up

The english is fine..... its my understanding of the formula that is causing the problem

I think this column G in the 'last 2 games tab' is causing me some trouble. if we dont need it i would like to remove it. if we can go back to the first sheet you supplied, i will run through what I am doing and hopefully you can tell me what im doing wrong.

* I enter the new teams in column A
* I copy the date formula down column B
* I can see from the formula's in cell range B2 to B5, that the ROW number in the formula is sequenced B2=B1 B3=B2 B4=B1 B5=B2...... So i alter the formula in cell B6 so that it reads =LARGE(IF(games!$A$2:$A$1000='Last 2 games'!A6,games!$C$2:$C$100),ROW(B1))
* However the value is wrong. I then notice that excel has removed the {} from the start and the end of the formula... So i put then back in and im left with a string of text, rather than a working formula.

I have left cells from B7 to B11 so you can see the missing {} and the effect they have.

The version of excel I am using is on office 10... Version number 14.0.6123.5001 (incase it makes any difference)

Again... really appreciate your time helping me with this.

thanks for that..... i will have a read of it shortly.

Originally Posted by benishiryo

hi Backroomgeeza. i converted your "games" worksheet data to a Table so that the range is always dynamic. for the data, i did some array formulas. hope it helps~ go Gunners~

Hi Benishiryo.... Using your template, i now have a few extra things that i need to do. i was wondering if you could help me out. I now need to do the following

* An additional column has been added to the table called FTR. I would like to also return the results for this in column F of the 'last 2 H games tab'

* I would also like to have this data for the away teams in a new tab i have named this 'last 2 A Games.... I have also tried adding the formulas in myself, but they don't seem to be working

Originally Posted by Fotis1991

And?

Hi Fotis

I managed to follow the steps in your spreadsheet and copy the data down so that the formulas worked. But unless i am mistaken, i would have had to copy this down 2 cells at a time, rather than dragging the formula down the column straight to the bottom. In my final sheet there will be over 800 lines in the sheet... and if i want to make the results 6 lines instead of 2, the sheet would be thousands long.... So although it worked, it would have meant alot of manual work.

And i still cant figure out why when i edit a formula excel removes the {} from the formula, making it error.... then when i put the {} back in manually, the formula becomes hard text.

I am very grateful for all the help you have given me on this. Thanks again.

Originally Posted by benishiryo

all those formulas are array formulas. so it means you need to go inside the formula & press CTRL + SHIFT + ENTER. you should see curly brackets surrounding it when done correctly. so that solves the FTR for Home games.

For the Away games, do the same thing. CTRL + SHIFT + ENTER for all the formulas. for FTR, you need to put "Away" instead of "Home"
=INDEX(Table1[FTR],MATCH($A2&$B2,Table1[AwayTeam]&Table1[Date],0))

it was the CTRL + SHIFT + ENTER that i wasnt aware of...... thats fixed it many thanks

Hi Fotis

unfortunately the proxy in work blocked me from viewing the link you provided in post 10.

@ Backroomgeeza

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

Hi Guys..... im back on this one again..... last time now i promise

I just need to make a small adjustment, but i cant seem to figure out how to do it. Instead of returning the data against 2 games for each club, i want to do it for 3.

I have added the extra lines into the spreadsheet for Arsenal and Liverpool.

I know its the formula in the 'date' cell... But i keep getting it wrong

if you could show me what the formula needs to be so that i can get the values returned for 3 matches that would be amazing.