Latest value based on most recent date

[JoshLyman]

Struggling to figure out what the best way is to return the COMPSTATUS which corresponds with the latest LEARNSTARTDATE for each Regno. I'm assuming it will be best achieved by using INDEX and SORT but can't quite decide where to start. An example of it working would be by returning the following for these Regno

A567883 = 1
A671172 = 3
B312726 = 1

2223-LearningDelivery (20).csv
	A											L									U										AE
1	Regno											LEARNSTARTDATE									COMPSTATUS										LATESTSTATUS
2	A567883											13-Oct-21									1
3	A567883											13-Oct-21									1
4	A671172											22-Jan-20									6
5	A671172											22-Jan-20									6
6	A671172											23-Jun-22									3
7	A671172											23-Jun-22									3
8	B018353											30-Sep-19									1
9	B018353											30-Sep-19									1
10	B119158											07-Oct-20									2
11	B119158											07-Oct-20									2
12	B216978											13-Oct-21									1
13	B216978											13-Oct-21									2
14	B218433											05-Oct-20									3
15	B218433											05-Oct-20									3
16	B219012											04-Oct-21									1
17	B219012											04-Oct-21									1
18	B312726											12-Oct-22									6
19	B312726											12-Oct-22									6
20	B312726											27-Feb-23									1
21	B312726											27-Feb-23									1
2223-LearningDelivery (20)

Thanks in advance.

 

[Fluff]

How about

=XLOOKUP(A2,$A$2:$A$21,$U$2:$U$21,"",0,-1)

 

[JoshLyman]

Thanks @Fluff. This works if I sort the LEARNSTARTDATE oldest to newst (which I do, generally), but if it's sorted newest to oldest it does the reverse. Not the absolute end of the world though, will just need to be careful when sorting!

 

[Fluff]

Ok, how about

=TAKE(SORT(FILTER($L$2:$U$21,$A$2:$A$21=A2),1,-1),1,-1)

 

[JoshLyman]

Perfect! Thanks, as always

 

[Fluff]

You're welcome & thanks for the feedback.