Latest Date

[1AG]

Sheet 2 Cell A2 contains the Company ID to lookup in Sheet 1 Column A and then return the latest Service Dates located in Column H. The formula will be in Sheet 2 A6. There may be more than one service date, therefore the Service Date needs to post in the next available row(s) A5:A100 according to latest date. I have tried several ways, using MAX and DateValue and cannot get formula,I would appreciate the help! Lots of intelligence in this forum, I am still learning, but hope to be able to contribute the same.

VLOOKUP(A2,Sheet 1!A:A,MAX(DATEVALUE(IF(MATCH(B1,Sheet1!Print_Titles,0),0))))


Sheet 2
A B
1 Company ID
2 A-3-BT
3
4
5 Service Date Emp Unit
6 3/3/20
7 1/11/19
8
9
10



Sheet 1
A B C D E F G H
1 Company ID Service Date
2 A-3-BT 10/11/19
3 A-3-BT 3/3/20
4 D-1-CC 1/11/17

 

[DanteAmor]

Try this array formula

Dante Amor
	A	B
1	Company ID
2	A-3-BT
3
4
5	Service Date Emp Unit
6	10/11/2019
7	03/03/2020
8	25/05/2020
9
10
Sheet2

Cell Formulas
Range	Formula
A6:A10	A6	=IFERROR(SMALL(IF(Sheet1!$A$2:$A$10=$A$2,Sheet1!$H$2:$H$10),ROWS($B$6:B6)),"")
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Dante Amor
	A							H
1	Company ID							Date
2	A-3-BT							10/11/2019
3	A-3-BT							03/03/2020
4	A-3-BT							25/05/2020
5	D-1-CC							01/11/2017
6
Sheet1