Get max status based on date

[Wishmaster89]

Hi All

I'd like to get the max status from column B based on the latest start date from column C. So the max status in this example should be as 12/01/2021 is the max start date. I thought i had the answer when i used max ifs in column t but then realised its obviously not taking the start date into account.

Book9
	A	B	C	D	E	F	G	H	I	J	K	L	M							T	U	V	W	X
1	ULN	Completion Status	Start Date	Start Year	Learning_Actual_End_Date converted	Withdrawal time frame(days)	Months	Standard	hybrid end year	Year	Restart indicator	max HEY	hybrid year with max							Max status	MIN Start date	Start year	Max status if 17/18	Max status if 18/19
2	1	1	18/07/2019	2018			not in a category	Maths	17/01/2021	20/21	1	30/11/2021	21/22							6	18/07/2019	18/19		6
3	1	3	12/01/2021	2020	19/07/2021	188	not in a category	Maths	30/11/2021	21/22	1	30/11/2021	21/22							6	18/07/2019	18/19		6
4	1	6	18/07/2019	2018	14/09/2020	424	13-18	Maths	17/01/2021	20/21	1	30/11/2021	21/22							6	18/07/2019	18/19		6
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2	Cell Value	duplicates	text	NO
A1:A1043475	Cell Value	duplicates	text	NO

 

[Fluff]

How about

=INDEX(SORT(B2:C4,2,-1),1,1)

 

[Wishmaster89]

How about

=INDEX(SORT(B2:C4,2,-1),1,1)

Thanks, that does work but i should of been clearer(sorry!), theres about 1000 rows in my actual data so the formula needs to take into account the different ID's(column A). Also, it needs to check that the standard(H) is the same. This is the formula i have in column T so far.
Your sort function may work but i can't think how to integrate it at the moment.

=MAXIFS(B:B,A:A,A2145,H:H,H2145)

 

[Fluff]

Ok, how about

=INDEX(SORT(FILTER($B$2:$C$5000,($A$2:$A$5000=A2)*($H$2:$H$5000=H2)),2,-1),1,1)

 

[Wishmaster89]

Ok, how about

=INDEX(SORT(FILTER($B$2:$C$5000,($A$2:$A$5000=A2)*($H$2:$H$5000=H2)),2,-1),1,1)

Works mostly but theres one thing. Sometimes in the data the start dates will be the same and in that case i need to return the max status. 6 in this example.

Book2
	A	B	C	D	E	F	G	H
1	ULN	Completion Status	Start Date	Start Year	Learning_Actual_End_Date converted	Withdrawal time frame(days)	Months	Standard
2	1	1	30/01/2018	2017		0	0	History
3	1	6	30/01/2018	2017	24/04/2019	449	13-18	History
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A3	Cell Value	duplicates	text	NO
A2	Cell Value	duplicates	text	NO
A1:A3	Cell Value	duplicates	text	NO

 

[Fluff]

Anything else you haven't mentioned?

 

[Wishmaster89]

Anything else you haven't mentioned?

I probably wasn't clear enough. Maybe this sample data is clearer?

so for ULN 1 the max status(b) should be 6 as the start dates and standard are the same.
For ULN 2 it should also be 6 as its similar to the above

ULN 3 has done two different courses ( English/sports) so it will need to return the status with the greater start date

ULN 3 should return 1 for the first two rows(same standard)
Then the bottom 2 of ULN 3 should return 6 as the greater date there is 15/02/202

Hope that makes sense?

Book2
	A	B	C	D	E	F	G	H
1	ULN	Completion Status	Start Date	Start Year	Learning_Actual_End_Date converted	Withdrawal time frame(days)	Months	Standard
2	1	1	30/01/2018	2017		0	0	History
3	1	6	30/01/2018	2017	24/04/2019	449	13-18	History
4	2	1	30/01/2018	2017		0	0	English
5	2	6	30/01/2018	2017	24/04/2019	449	13-18	English
6	3	1	30/01/2019	2017		0	0	English
7	3	6	30/01/2020	2017	24/04/2019	449	13-18	English
8	3	1	15/02/2020	2017	24/04/2019	449	13-18	Sports
9	3	6	15/02/2021	2017	24/04/2019	449	13-18	Sports
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A3	Cell Value	duplicates	text	NO
A2	Cell Value	duplicates	text	NO
A1:A3	Cell Value	duplicates	text	NO

 

[Fluff]

Why should ULN 3 & English return 1? The dates are the same & 6 is greater than 1

 

[Wishmaster89]

Why should ULN 3 & English return 1? The dates are the same & 6 is greater than 1

Sorry yes your right ULN 3 - English should return 6

 

[Fluff]

Ok, how about

=INDEX(SORT(FILTER($B$2:$C$5000,($A$2:$A$5000=A2)*($H$2:$H$5000=H2)),{2,1},-1),1,1)