Max Date and Return Value

[ddd2009]

Good afternoon

I return the value in sheet 1 column B from sheet 2, column C based on the max date for each ref in column A. I have manage to return the max date but cannot fathom out how to then return 11 for Ref 1, 8 for Ref 2 and 1 for Ref 3.

Any help is greatly appreciated

Thank you in advance
A

Sheet I

Sheet 2

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[ddd2009]

Details updated thank you

 

[Fluff]

Thanks for that, how about like

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H
1	1	01/01/2022	10
2	1	02/01/2022	12			1	03/01/2022	11
3	1	03/01/2022	11			2	05/01/2022	8
4	2	01/01/2022	4			3	06/01/2022	2
5	2	04/01/2022	6
6	2	05/01/2022	8
7	3	01/01/2022	1
8	3	06/01/2022	2
9	3	05/01/2022	1
10
Sheet2

Cell Formulas
Range	Formula
G2:H4	G2	=INDEX(SORT(FILTER($B$1:$C$9,$A$1:$A$9=F2),1,-1),1)
Dynamic array formulas.

 

[ddd2009]

That's perfect thank you so much

 

[Fluff]

You're welcome & thanks for the feedback.