Help amending VBA Code

[purceld2]

Hi

In theory it should be simple but I cannot seem to get it to work. The VBA below correctly retrieve a date, what I would like to do is to also retrieve two extra bits of data from the same row.

Which The "Territory Map Number" and "Territory Map Name" on the same row.

Could someone show how this can be done please.

Sub LastDate1()
Dim Max_date As Date
Max_date = [MIN(IF(D3:D36<>"ALLOCATED",C3:C36))]
MsgBox Max_date
End Sub

Perry Hill Territory Spreadsheet.xlsm
	A	B	C	D	E
1	Territory Map Number	Territory Map Name	Last Returned	Allocated	Allocated To
2	1	Dacres Road	06/12/2020	ALLOCATED
3	2	Valentine Court	13/03/2021
4	3	Bampton Road	09/10/2021
5	4	Pearfield Rd	02/05/2021
6	5	Sydenham Station	10/10/2021
7	6	Silverdale Road	17/01/2021	ALLOCATED
8	7	The Thorpes	15/01/2021	ALLOCATED
9	8	Burghill Road	24/03/2021
10	9	DeFrene Road	03/03/2021
11	10	Sydenham Rd	19/03/2021
Map Status

 

[lrobbo314]

How about this?

Sub LastDate1()
x = [INDEX(A2:A11 & ": " &  B2:B11,MATCH(MIN(IF(D2:D11<>"ALLOCATED",C2:C11)),C2:C11,0))]
MsgBox x(1)
End Sub

 

[purceld2]

How about this?

Sub LastDate1()
x = [INDEX(A2:A11 & ": " &  B2:B11,MATCH(MIN(IF(D2:D11<>"ALLOCATED",C2:C11)),C2:C11,0))]
MsgBox x(1)
End Sub

Hi Irobbo
Thanks so much for your prompt reply and worked just fine, just one some issue it displays everything except the date could you amend to include this please

 

[lrobbo314]

How about...

Sub LastDate1()
x = [INDEX(A2:A11 & ": " & B2:B11 & " - " & TEXT(C2:C11,"MM/DD/YYYY"),MATCH(MIN(IF(D2:D11<>"ALLOCATED",C2:C11)),C2:C11,0))]
MsgBox x(1)
End Sub

 

[purceld2]

How about...

Sub LastDate1()
x = [INDEX(A2:A11 & ": " & B2:B11 & " - " & TEXT(C2:C11,"MM/DD/YYYY"),MATCH(MIN(IF(D2:D11<>"ALLOCATED",C2:C11)),C2:C11,0))]
MsgBox x(1)
End Sub

Perfect
Thanks for all your help