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

James8761

Board Regular
Joined
Apr 24, 2012
Messages
139
Office Version
  1. 2019
Platform
  1. Windows
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.

1597917922615.png



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.

1597917806887.png


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.

1597917893391.png


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
 

Attachments

  • 1597917688012.png
    1597917688012.png
    49.6 KB · Views: 5

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
884
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Oct 20, 2009
Messages
8,245
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Apr 24, 2012
Messages
139
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Book145.xlsx
ABCDEFGHIJKL
1
2
3
4HomeAwayLast 5 Games Home TeamLast 5 Games Away Team
5LiverpoolLeeds2-13-24-02-02-00-40-11-12-01-0
Sheet2
 

James8761

Board Regular
Joined
Apr 24, 2012
Messages
139
Office Version
  1. 2019
Platform
  1. Windows
Book145.xlsx
ABCDEFGH
1SeasonDateRoundHomeAwayHGFTAGFTFull Time
22009/08/201901LiverpoolNorwich414-1
32024/08/201903LiverpoolArsenal313-1
42014/09/201905LiverpoolNewcastle313-1
52005/10/201908LiverpoolLeicester212-1
62027/10/201910LiverpoolTottenham212-1
72010/11/201912LiverpoolMan City313-1
82030/11/201914LiverpoolBrighton212-1
92004/12/201915LiverpoolEverton525-2
102014/12/201917LiverpoolWatford202-0
112029/12/201920LiverpoolWolves101-0
122002/01/202021LiverpoolSheffield Utd202-0
132019/01/202023LiverpoolMan Utd202-0
142001/02/202025LiverpoolSouthampton404-0
152024/02/202027LiverpoolWest Ham323-2
162007/03/202029LiverpoolBournemouth212-1
172004/08/201901Bristol CityLeeds131-3
182017/08/201903WiganLeeds020-2
192024/08/201905StokeLeeds030-3
202015/09/201907BarnsleyLeeds020-2
212028/09/201909CharltonLeeds101-0
222005/10/201911MillwallLeeds212-1
232022/10/201913PrestonLeeds111-1
242026/10/201914Sheffield WedLeeds000-0
252023/11/201917LutonLeeds121-2
262026/11/201918ReadingLeeds010-1
272007/12/201920HuddersfieldLeeds020-2
282021/12/201923FulhamLeeds212-1
292029/12/201925BirminghamLeeds454-5
302001/01/202026West BromwichLeeds111-1
312018/01/202028Queens ParkLeeds101-0
322008/02/202031NottinghamLeeds202-0
332011/02/202032BrentfordLeeds111-1
342026/02/202035MiddlesbroughLeeds010-1
352029/02/202036HullLeeds040-4
ALL
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,934
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

How about
+Fluff New.xlsm
ABCDEFGHIJKL
1
2
3
4HomeAwayLast 5 Games Home TeamLast 5 Games Away Team
5LiverpoolLeeds2-13-14-02-02-00-40-11-12-01-0
6
Sheet2
Cell Formulas
RangeFormula
C5:G5C5=INDEX(SORT(FILTER(All!B2:H9000,All!D2:D9000=A5),1,-1),SEQUENCE(,5),7)
H5:L5H5=INDEX(SORT(FILTER(All!B2:H9000,All!E2:E9000=B5),1,-1),SEQUENCE(,5),7)
Dynamic array formulas.
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
884
Office Version
  1. 365
Platform
  1. Windows
As your data is formatted as Date so change the Score format to General

Try this

Book1
ABCDEFGHIJKL
4HomeAwayLast 5 Games Home TeamLast 5 Games Away Team
5LiverpoolLeeds02-Jan03-Feb4-02-02-00-40-101-Jan2-01-0
6
7 43,832.00 43,864.00 4-0 2-0 2-0
8
9
10
Sheet1
Cell Formulas
RangeFormula
C7:G7C7=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

Board Regular
Joined
Apr 24, 2012
Messages
139
Office Version
  1. 2019
Platform
  1. Windows
How about
+Fluff New.xlsm
ABCDEFGHIJKL
1
2
3
4HomeAwayLast 5 Games Home TeamLast 5 Games Away Team
5LiverpoolLeeds2-13-14-02-02-00-40-11-12-01-0
6
Sheet2
Cell Formulas
RangeFormula
C5:G5C5=INDEX(SORT(FILTER(All!B2:H9000,All!D2:D9000=A5),1,-1),SEQUENCE(,5),7)
H5:L5H5=INDEX(SORT(FILTER(All!B2:H9000,All!E2:E9000=B5),1,-1),SEQUENCE(,5),7)
Dynamic array formulas.

Excellent, thank you so much!
 

James8761

Board Regular
Joined
Apr 24, 2012
Messages
139
Office Version
  1. 2019
Platform
  1. Windows
As your data is formatted as Date so change the Score format to General

Try this

Book1
ABCDEFGHIJKL
4HomeAwayLast 5 Games Home TeamLast 5 Games Away Team
5LiverpoolLeeds02-Jan03-Feb4-02-02-00-40-101-Jan2-01-0
6
7 43,832.00 43,864.00 4-0 2-0 2-0
8
9
10
Sheet1
Cell Formulas
RangeFormula
C7:G7C7=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!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,611
Members
414,080
Latest member
penguin23

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top