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

 

[CA_Punit]

Can you post your data through xl2bb addin. This way we dont have to type all the data in sheet.

Please find xl2bb addin to the right most side of the reply box--->Download it-----> add it as addin------->You will find a ribbon in your Excel with Mr Excel

 

[steve the fish]

You will be able to do this. Could you though provide sample data that can be copied and pasted into excel? You will certainly get more replies if you do.

 

[James8761]

I'll try again

 

[James8761]

Book145.xlsx
	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-2	4-0	2-0	2-0	0-4	0-1	1-1	2-0	1-0
Sheet2

 

[James8761]

Book145.xlsx
	A	B	C	D	E	F	G	H
1	Season	Date	Round	Home	Away	HGFT	AGFT	Full Time
2	20	09/08/2019	01	Liverpool	Norwich	4	1	4-1
3	20	24/08/2019	03	Liverpool	Arsenal	3	1	3-1
4	20	14/09/2019	05	Liverpool	Newcastle	3	1	3-1
5	20	05/10/2019	08	Liverpool	Leicester	2	1	2-1
6	20	27/10/2019	10	Liverpool	Tottenham	2	1	2-1
7	20	10/11/2019	12	Liverpool	Man City	3	1	3-1
8	20	30/11/2019	14	Liverpool	Brighton	2	1	2-1
9	20	04/12/2019	15	Liverpool	Everton	5	2	5-2
10	20	14/12/2019	17	Liverpool	Watford	2	0	2-0
11	20	29/12/2019	20	Liverpool	Wolves	1	0	1-0
12	20	02/01/2020	21	Liverpool	Sheffield Utd	2	0	2-0
13	20	19/01/2020	23	Liverpool	Man Utd	2	0	2-0
14	20	01/02/2020	25	Liverpool	Southampton	4	0	4-0
15	20	24/02/2020	27	Liverpool	West Ham	3	2	3-2
16	20	07/03/2020	29	Liverpool	Bournemouth	2	1	2-1
17	20	04/08/2019	01	Bristol City	Leeds	1	3	1-3
18	20	17/08/2019	03	Wigan	Leeds	0	2	0-2
19	20	24/08/2019	05	Stoke	Leeds	0	3	0-3
20	20	15/09/2019	07	Barnsley	Leeds	0	2	0-2
21	20	28/09/2019	09	Charlton	Leeds	1	0	1-0
22	20	05/10/2019	11	Millwall	Leeds	2	1	2-1
23	20	22/10/2019	13	Preston	Leeds	1	1	1-1
24	20	26/10/2019	14	Sheffield Wed	Leeds	0	0	0-0
25	20	23/11/2019	17	Luton	Leeds	1	2	1-2
26	20	26/11/2019	18	Reading	Leeds	0	1	0-1
27	20	07/12/2019	20	Huddersfield	Leeds	0	2	0-2
28	20	21/12/2019	23	Fulham	Leeds	2	1	2-1
29	20	29/12/2019	25	Birmingham	Leeds	4	5	4-5
30	20	01/01/2020	26	West Bromwich	Leeds	1	1	1-1
31	20	18/01/2020	28	Queens Park	Leeds	1	0	1-0
32	20	08/02/2020	31	Nottingham	Leeds	2	0	2-0
33	20	11/02/2020	32	Brentford	Leeds	1	1	1-1
34	20	26/02/2020	35	Middlesbrough	Leeds	0	1	0-1
35	20	29/02/2020	36	Hull	Leeds	0	4	0-4
ALL

 

[Fluff]

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.

 

[CA_Punit]

As your data is formatted as Date so change the Score format to General

Try this

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
4	Home	Away	Last 5 Games Home Team					Last 5 Games Away Team
5	Liverpool	Leeds	02-Jan	03-Feb	4-0	2-0	2-0	0-4	0-1	01-Jan	2-0	1-0
6
7			43,832.00	43,864.00	4-0	2-0	2-0
8
9
10
Sheet1

Cell Formulas
Range	Formula
C7:G7	C7	=INDEX(Sheet2!$H$2:$H$35,MATCH(AGGREGATE(14,6,IF($A$5=Sheet2!$D$2:$D$35,Sheet2!$B$2:$B$35),{1,2,3,4,5}),IF($A$5=Sheet2!$D$2:$D$35,Sheet2!$B$2:$B$35),0))
Dynamic array formulas.

With Control Shift and Enter if you are not using office 365.

 

[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.

Excellent, thank you so much!

 

[James8761]

As your data is formatted as Date so change the Score format to General

Try this

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
4	Home	Away	Last 5 Games Home Team					Last 5 Games Away Team
5	Liverpool	Leeds	02-Jan	03-Feb	4-0	2-0	2-0	0-4	0-1	01-Jan	2-0	1-0
6
7			43,832.00	43,864.00	4-0	2-0	2-0
8
9
10
Sheet1

Cell Formulas
Range	Formula
C7:G7	C7	=INDEX(Sheet2!$H$2:$H$35,MATCH(AGGREGATE(14,6,IF($A$5=Sheet2!$D$2:$D$35,Sheet2!$B$2:$B$35),{1,2,3,4,5}),IF($A$5=Sheet2!$D$2:$D$35,Sheet2!$B$2:$B$35),0))
Dynamic array formulas.

With Control Shift and Enter if you are not using office 365.

Thank you very much. Much appreciated!