Advice with Finding Data

[purceld2]

Hi

I am look for a formula possibly a lookup to find the last row in a spreadsheet for each group with the latest return date.

I have attached some sample data. For each "Territory" I need to return the row of data with the latest "Returned" date. So it would be row 18 based for Territory 1 based on the 06/12/2020 being the latest date.

So need to do this for all the other Territories in the spreadsheet which currently goes up to 35.

Assignments_All.xlsx
	A	B	C	D	E	F	G	H
2	Territory	Campaign	Publisher	Assigned	Returned	Days Assigned	Last Allocated	Status
3	1			14/05/2016	25/06/2016	42	1937	Overdue
4	1			05/08/2016	19/08/2016	14	1882	OK
5	1			07/11/2016	26/12/2016	49	1753	Overdue
6	1			08/03/2017	26/03/2017	18	1663	OK
7	1			26/03/2017	07/05/2017	42	1621	Overdue
8	1			29/07/2017	15/10/2017	78	1460	Overdue
9	1			10/02/2018	24/02/2018	14	1328	OK
10	1			21/07/2018	28/07/2018	7	1174	OK
11	1			01/11/2018	03/01/2019	63	1015	Overdue
12	1			19/01/2019	25/02/2019	37	962	Overdue
13	1			03/04/2019	18/04/2019	15	910	OK
14	1			28/06/2019	28/07/2019	30	809	OK
15	1			07/09/2019	18/09/2019	11	757	OK
16	1			07/09/2019	18/09/2019	11	757	OK
17	1			12/12/2019	04/01/2020	23	649	OK
18	1	1		26/09/2020	06/12/2020	71	312	Overdue
19	1	2		19/09/2021		25	ALLOCATED	OK
20	2			22/04/2016	08/07/2016	77	1924	Overdue
21	2			06/08/2016	14/08/2016	8	1887	OK
22	2			03/10/2016	03/12/2016	61	1776	Overdue
23	2			21/01/2017	15/03/2017	53	1674	Overdue
24	2			29/03/2017	02/04/2017	4	1656	OK
25	2			12/05/2017	18/06/2017	37	1579	Overdue
26	2			22/07/2017	30/07/2017	8	1537	OK
27	2			02/09/2017	06/10/2017	34	1469	Overdue
28	2			06/12/2017	15/01/2018	40	1368	Overdue
Assignment

Cell Formulas
Range	Formula
F3:F28	F3	=IF(ISBLANK(E3),TODAY()-D3,E3-D3)
G3:G28	G3	=IF(ISBLANK(E3),"ALLOCATED",TODAY()-E3)
H3:H28	H3	=IF(F3>30,"Overdue","OK")

 

[Peter_SSs]

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’)

 

[Peter_SSs]

What about using a Pivot Table with Returned values set to 'Max'

21 10 14.xlsm
	J	K	L
1
2		Max of Returned
3		Territory	Total
4		1	6/12/2020
5		2	15/01/2018
6		Grand Total	6/12/2020
7
Latest

 

[Peter_SSs]

Thanks for updating your profile.
.. and glad to see that a Pivot Table seemed OK for you.