Look Up Help Required with newest date

[Celticshadow]

Hi All

I would like to have a formula that looks up the horse Priory in workbook 1 and finds its speed value in workbook 2 (col H)
from the most recent date and place it in workbook 1 col H2. Unfortunately I am unable to sort workbook two but can sort workbook one in ascending order by horse name, also the dates in workbook two are in ascending order. I have attached an example as below and hope that helps with my query. Many thanks in advance.

Regards


Workbook 1

ID	Rnnrs	DATE	COURSE	TIME	NO	HORSE	Speed Fig Required
1	5	10 June 2015	Beverley	1400	4	Priory	15
1	5	10 June 2015	Beverley	1400	6	Lolamotion
1	5	10 June 2015	Beverley	1400	1	Mustnt Grumble

<tbody>
</tbody>

Workbook 2

ID	Rnnrs	DATE	COURSE	TIME	NO	HORSE	SPEED
870	5	07 April 2015	Lingfield	1510	5	Priory	0
2,001	9	09 May 2015	Goodwood	1445	9	Priory	80
3,126	8	01 June 2015	Windsor	1755	5	Priory	15

<tbody>
</tbody>

 

[Celticshadow]

Hi Aladin

Apologies for getting back sooner but I had a bit of an emergency to deal with and have only just got back to everyday musings. When I enter your above formula it seems to returning the latest occurrence of the speed figure being 15 not the third being 60 unless I am reading the input of the formula incorrectly. Many thanks

Regards

 

[Celticshadow]

Hi Aladin Akyurek

My apologies for coming back to this thread but would it be possible to get the 3rd most recent speed figure which from the above example would be a 60 on the 15 May 15. Many thanks for your previous help and my apologies but I would just like to be able to get a formula solution as I am a little stuck.

Regards

 

[Celticshadow]

Hi Aladin Akyurek

My apologies for coming back to this thread but would it be possible to get the 3rd most recent speed figure which from the above example would be a 60 on the 15 May 15. Many thanks for your previous help and my apologies but I would just like to be able to get a formula solution as I am a little stuck.

Regards

Hi Forum

As far as I can see Aladin Akyurek has not posted for sometime (I do hope all is well) and with that in mind would any other members of forum be kind enough to take a look at the above to see if they could help with a formula solution for me. Many thanks.

Regards

 

[Peter_SSs]

You now have MS 365 so now have a lot more functions available to you than back in 2015.
See if this is any use to return (up to) the last 3 speed ratings. Just copy the formula down column H.
Check workbook and worksheet names and ranges.

Workbook 2.xlsx
	A	B	C				G	H
1	ID	Rnnrs	DATE				HORSE	SPEED
2	870	5	7-Apr-15				Priory	0
3	2,001	9	9-May-15				Priory	80
4	3,126	8	15-May-15				Priory	60
5			30-May-15				Lolamotion	80
6			30/05/2015				Priory	15
7			1/06/2015				Priory	15
8
Sheet1

Workbook 1.xlsx
	G	H	I	J
1	HORSE	Last 3 speeds
2	Priory	15	15	60
3	Lolamotion	80
4	Mustnt Grumble
Sheet1

Cell Formulas
Range	Formula
H2:J2,H3:H4	H2	=LET(d,'[Workbook 2.xlsx]Sheet1'!C$2:H$10000,TRANSPOSE(TAKE(TAKE(SORT(FILTER(d,CHOOSECOLS(d,5)=G2,""),1,-1),,-1),3)))
Dynamic array formulas.

 

[Celticshadow]

Hi Peter_SSs

Many thanks for replying it is much appreciated. May I ask what the formula is saying in laymans terms, I am particularly curious as to what the "d" is saying in the formula. The reason I ask is I am wondering if I can adapt it to pick up other figures on my spreadsheet along with the speed figures it is already picking up. My apologies for further questions and also for lateness in getting back to your goodself.


Regards

 

[Peter_SSs]

May I ask what the formula is saying in laymans terms,

1. FILTER columns C:H of WB2 to show rows for the relevant horse only (ie where the 5th column of C:H is the correct horse)
2. SORT that data by the first column of C:H (Date) descending so most recent is at the top
3. TAKE ,,-1 means take the last column (ie the speed column)
4. TAKE ,3 means take (up to) the first 3 rows of that column
5. TRANSPOSE to turn it from vertical to horizontal

I am particularly curious as to what the "d" is saying in the formula.

=LET(d,'[Workbook 2.xlsx]Sheet1'!C$2:H$10000,TRANSPOSE(TAKE(TAKE(SORT(FILTER(d,CHOOSECOLS(d,5)=G2,""),1,-1),,-1),3)))

The d is just like a variable name. It is set at the beginning of the LET statement to simply store that the data is in C2:H10000 of Sheet1 in WB 2 (range, sheet name and workbook name can be edited by you if required) and simply saves using that longer text twice later in the formula where the two blue "d"s are. Using LET like this makes writing the formula a bit shorter and can make formulas more efficient by not having to reassess a particular calculation/range etc.

So this version without the d and the LET would do the same job.
=TRANSPOSE(TAKE(TAKE(SORT(FILTER('[Workbook 2.xlsx]Sheet1'!C$2:H$10000,CHOOSECOLS('[Workbook 2.xlsx]Sheet1'!C$2:H$10000,5)=G2,""),1,-1),,-1),3))

More information about LET and variable names here: LET function - Microsoft Support

 

[Celticshadow]

Hi Peter_SSs

Apologies for such lateness, I thought I had marked this as the perfect solution as it is, many many thanks for your time and knowledge so selflessly given.

Kind Regards

 

[Peter_SSs]

Apologies for such lateness

Better late than never.
You are very welcome.