A search formula (like index match) that can pull the most recent date when multiple matches may occur based on the criteria figure.

[Maverick007]

I am trying to write a formula that will find the most recent date using the criteria of the Employee ID. Normally I would use an Index-Match function, but as there are multiple possible dates based on a singular ID I am unsure how to make a dynamic formula such it returns the most recent date as I believe it will check top to bottom and return the first date that matches the ID criteria. Note as well, in this case an Employee can have multiple IDs (I don't think that will impact the formula in question but wanted to point it out just in case). Thanks in advance for any help!

 

[Cubist]

Try:

Book1
	A	B	C	D	E	F	G
1
2
3		Employee ID	Name	Date		Employee ID	Date
4		ID-1		1/1/2019		ID-1	12/31/2022
5		ID-1		6/1/2020		ID-2	4/15/2023
6		ID-1		12/31/2022		ID-3	9/1/2023
7		ID-2		4/15/2023		ID-4	12/31/2023
8		ID-3		8/12/2022
9		ID-3		9/1/2023
10		ID-4		6/1/2021
11		ID-4		8/15/2021
12		ID-4		10/31/2023
13		ID-4		12/31/2023
Sheet3

Cell Formulas
Range	Formula
G4:G7	G4	=MAXIFS(D4:D13,B4:B13,F4:F7)
Dynamic array formulas.

 

[MARZIOTULLIO]

G4=IF(F4<>"",MAXIFS($D$4:$D$13,$B$4:$B$13,F4),"")

Copy down