Find 5 most recent bits of data via a Date - Most Recent first

[James8761]

Hi All,


Another project I am working on. I have a database of football results. I am trying to create a visual via a formula rather than linking manually.

I am trying to get a football teams most recent last 5 results. The 5 most recent Home results for the team playing at home today and the 5 most recent Away results for the team playing Away today.

I’ve set a dummy game up, Liverpool v Leeds.

On Sheet 2 I have manually linked the end result I would like to do. Is there a way I can do this via a formula though?

My football database is in the tab titled ALL.

This is the end result I would like to see.

This is the copy of Leeds Away data. the columns I am interested in picking data up from is Column B, the date. (I have this ordered oldest to newest). Column E the Away team name and Column H, the Full time Result.

Similarly, for Liverpool the columns I am interested in picking data up from is Column B, the date. (I have this ordered oldest to newest). Column D the Home team name and Column H, the Full time Result.

Is there a way I can put formulas in Cells C5:L5 of Sheet 2 to capture the data I want please?

Any help greatly appreciated.

Kind regards,
James

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[James8761]

How about

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1
2
3
4	Home	Away	Last 5 Games Home Team					Last 5 Games Away Team
5	Liverpool	Leeds	2-1	3-1	4-0	2-0	2-0	0-4	0-1	1-1	2-0	1-0
6
Sheet2

Cell Formulas
Range	Formula
C5:G5	C5	=INDEX(SORT(FILTER(All!B2:H9000,All!D2:D9000=A5),1,-1),SEQUENCE(,5),7)
H5:L5	H5	=INDEX(SORT(FILTER(All!B2:H9000,All!E2:E9000=B5),1,-1),SEQUENCE(,5),7)
Dynamic array formulas.

Hi Fluff,

I hope you are well. When I e-mailed yesterday I was on the work laptop, now I am using my personal one. (I'll be using the personal one for this project).

I have this version of Excel at home.......would you know a formula that would work in the below version please? Kind regards, James

 

[Fluff]

Have you tried the formula from CA_Punit?

 

[James8761]

Have you tried the formula from CA_Punit?

I did, but it gave me the same answer in all 5 cells, just the most recent result.

 

[Fluff]

Did you confirm it with Ctrl Shift Enter, rather than just Enter? If so you should see the formula wrapped in {}

 

[James8761]

Did you confirm it with Ctrl Shift Enter, rather than just Enter? If so you should see the formula wrapped in {}

HI, yes, I have those around it. I have moved the data around and now I am placing the formula in Cell AW5 of Tab called Sheet2, the data is on the tab titled ALL. I've copied it down a few rows, but ti just gives me the most recent result 5 times.

Database 200821.xlsx
	I
21946	2
ALL

Database 200821.xlsx
	BA
5	2-1
Sheet2

Cell Formulas
Range	Formula
BA5	BA5	=INDEX(ALL!$W$2:$W$350000,MATCH(AGGREGATE(14,6,IF($A5=ALL!$D$2:$D$350000,ALL!$B$2:$B$350000),{1,2,3,4,5}),IF($A5=ALL!$D$2:$D$350000,ALL!$B$2:$B$350000),0))
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AX5:BA11	Expression	=$BJ5="L"	text	NO
AX5:BA11	Expression	=$BJ5="D"	text	NO
AX5:BA11	Expression	=$BJ5="W"	text	NO

 

[Fluff]

Ok, with your original layout, how about

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2
3
4	Home	Away	Last 5 Games Home Team					Last 5 Games Away Team
5	Liverpool	Leeds	2-1	3-1	4-0	2-0	2-0	0-4	0-1	1-1	2-0	1-0
6
7
Sheet2

Cell Formulas
Range	Formula
C5:G5	C5	=INDEX(All!$H$2:$H$35,AGGREGATE(14,6,(ROW(All!$H$2:$H$35)-ROW(All!$H$2)+1)/(All!$D$2:$D$35=$A5),COLUMNS($C5:C5)))
H5:L5	H5	=INDEX(All!$H$2:$H$35,AGGREGATE(14,6,(ROW(All!$H$2:$H$35)-ROW(All!$H$2)+1)/(All!$E$2:$E$35=$B5),COLUMNS($H5:H5)))

 

[James8761]

Ok, with your original layout, how about

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2
3
4	Home	Away	Last 5 Games Home Team					Last 5 Games Away Team
5	Liverpool	Leeds	2-1	3-1	4-0	2-0	2-0	0-4	0-1	1-1	2-0	1-0
6
7
Sheet2

Cell Formulas
Range	Formula
C5:G5	C5	=INDEX(All!$H$2:$H$35,AGGREGATE(14,6,(ROW(All!$H$2:$H$35)-ROW(All!$H$2)+1)/(All!$D$2:$D$35=$A5),COLUMNS($C5:C5)))
H5:L5	H5	=INDEX(All!$H$2:$H$35,AGGREGATE(14,6,(ROW(All!$H$2:$H$35)-ROW(All!$H$2)+1)/(All!$E$2:$E$35=$B5),COLUMNS($H5:H5)))

That's excellent, does exactly what I want it to. Thank you very much and thank you for your patience!

 

[Fluff]

You're welcome & thanks for the feedback.