find the value of a record with latest date

[logstarter]

Hi,

I have a question to find the staff value of the latest production date for the same product. I know how to find the latest production data by using max and if formula. But I would like to ask how to write the formula to get the staff name of the latest production date with the same product id. The circle value below is the expected value.

I have the following data

production date	product id	staff	staff at latest production date of the same product
1/1/2020	A	john	(john)
13/12/2019	B	sam	(ken)
16/12/2019	A	jane	(john)
19/12/2019	B	ken	(ken)
1/12/2019	A	sam	(john)

Thank you for your help.

 

[AlanY]

try this

Book1
	A	B	C	D
1	production date	product id	staff	staff at latest production date of the same product
2	01/01/2020	A	john	john
3	13/12/2019	B	sam	ken
4	16/12/2019	A	jane	john
5	19/12/2019	B	ken	ken
6	01/12/2019	A	sam	john
Sheet2

Cell Formulas
Range	Formula
D2:D6	D2	=INDEX($C$2:$C$6,MATCH(MAXIFS($A$2:$A$6,$B$2:$B$6,B2),$A$2:$A$6,0))

 

[Peter_SSs]

try this

Hi Alan
I think that your solutions relies on "chance" in relation to what dates might appear where in column A. Try changing cell A3 to 1/01/2020

This is my suggestion

Book1
	A	B	C	D
1	production date	product id	staff	staff of latest
2	1/01/2020	A	john	john
3	13/12/2019	B	sam	ken
4	16/12/2019	A	jane	john
5	19/12/2019	B	ken	ken
6	1/12/2019	A	sam	john
Staff

Cell Formulas
Range	Formula
D2:D6	D2	=INDEX(C$2:C$6,MATCH(MAXIFS(A$2:A$6,B$2:B$6,B2)&"|"&B2,INDEX(A$2:A$6&"|"&B$2:B$6,0),0))

 

[AlanY]

Hi Alan
I think that your solutions relies on "chance" in relation to what dates might appear where in column A. Try changing cell A3 to 1/01/2020

This is my suggestion

Book1
	A	B	C	D
1	production date	product id	staff	staff of latest
2	1/01/2020	A	john	john
3	13/12/2019	B	sam	ken
4	16/12/2019	A	jane	john
5	19/12/2019	B	ken	ken
6	1/12/2019	A	sam	john
Staff

Cell Formulas
Range	Formula
D2:D6	D2	=INDEX(C$2:C$6,MATCH(MAXIFS(A$2:A$6,B$2:B$6,B2)&"|"&B2,INDEX(A$2:A$6&"|"&B$2:B$6,0),0))

well spotted, thanks