Find last 10 latest dates

[wladoD]

Hi all,

I have a bit of challenge.

I have a sport league and I would like to find out last 10 games played for all the teams based on date.
I was able to figure it out with Large function but only for one column. I would need to have last 10 games based on dates from column C and J for all the teams and column K must say play.
Not necessary there are 10 last games played for each team.

Anybody could help me please.

Thank you

[sandy666]

maybe

• click on header Date
• Data - Filter
• Select date column and change temporary to Number format
• Select filter and choose TopTen
• then change Number format to Date

I can't say something about col. K because all there is "Played"

but if you want you can filter Col. K by Played and then all above

[wladoD]

Thank you for input.
But I would need to make formula as I need to use it as value for another formula.

Thank you

[sandy666]

No problem
Welcome

[FDibbins]

Try this, wherever you want the results...
=IF(ROWS($A$1:A1)>10,"",INDEX(A:A,MATCH(LARGE($A:$A,ROWS($A$1:A1)),$A:$A,0)))
copied down and format as needed

[wladoD]

Thank you it works. It gives 10 dates from largest.
But I would need to match this results with team. one for away for for home please.

[FDibbins]

Update your sample WB with your expected results please (input manually if needed)

[wladoD]

Hi,

Thank you sorry for my late answer.

So I have updated the file only with two teams.

I would like to have 10 last dates for played matches for each team.

In file you have 2 teams - Toronto and Winnipeg.
I used this formula to get the latest date.

Please Login or Register  to view this content.

This for for home team (C column)

Results for both teams are in column P & Q.

The final results should have look at both away (C) and home (L) teams dates.
In fact result should should show same last date for both team as they played together.

Thank you

[shukla.ankur281190]

May be Try

P2

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[samba_ravi]

p2=IFERROR(LARGE(IF(OR($C$2:$C$7=$O2,$L$2:$L$7=$O2),$A$2:$A$7),COLUMNS($P2:P2)),"")
Try this array formula and copy across

[wladoD]

Brilliant guys both formulas work like charm.
Now I got the last dates how could i use it in formula to get date from OUT1 or OUT2 based on date and team name please.

Thank you