vlookup max date

[KAYE04]

hi i am having a trouble to figure out how to include the max function. my data sets are like this

for my summary I will identify per month who is the hired employee. I want my summary like this

For january it is not angelo because he is hired january 5 it should be mark because he is hired jan 31 the latest date
The header should be per month not specific date because I have lots of data BUD-002 upto BUD-600

i hope some one can help me

 

[Peter_SSs]

I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

Is this what you are after?

24 01 19.xlsm
	A	B	C	D	E	F	G	H
1	control #	Name	Date		Control #	January	February	March
2	BUD-001	Name 1	5/01/2024		BUD-001	Name 2	Name 3	Name 4
3	BUD-001	Name 2	31/01/2024		BUD-002		Name 6	Name 7
4	BUD-001	Name 3	25/02/2024
5	BUD-001	Name 4	15/03/2024
6	BUD-002	Name 5	15/02/2024
7	BUD-002	Name 6	18/02/2024
8	BUD-002	Name 7	16/03/2024
Latest hire

Cell Formulas
Range	Formula
F2:H3	F2	=IFERROR(INDEX($B:$B,AGGREGATE(14,6,ROW($C$2:$C$8)/(($A$2:$A$8=$E2)*(TEXT($C$2:$C$8,"mmmm")=F$1)),1)),"")

 

[awoohaw]

I came up with this way, not sure if it is as comprehensive as @Peter_SSs 's version.
It requires you to have a date formatted as long month name for the column headers.

Book1
	A	B	C	D
1		control #	Name	Date Hired
2	A1	BUD-001	Angelo	2024-01-05
3	A2	BUD-001	Mark	2024-01-31
4	A3	BUD-001	Carlo	2024-02-25
5	A4	BUD-001	Alvin	2024-03-15
6
7	control#	January	February	March
8	BUD-001	Mark	Carlo	Alvin
9
10		January	February	March
11	BUD-001	Mark	Carlo	Alvin
Sheet1

Cell Formulas
Range	Formula
B10	B10	=DATE(2024,1,1)
C10:D10	C10	=EDATE(B10,1)
B11:D11	B11	=INDEX($C$2:$C$5,MATCH(MAX((MONTH($D$2:$D$5)=MONTH(B10))*($D$2:$D$5))&$A11,$D$2:$D$5&$B$2:$B$5,0))

 

[KAYE04]

I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

Is this what you are after?

24 01 19.xlsm
	A	B	C	D	E	F	G	H
1	control #	Name	Date		Control #	January	February	March
2	BUD-001	Name 1	5/01/2024		BUD-001	Name 2	Name 3	Name 4
3	BUD-001	Name 2	31/01/2024		BUD-002		Name 6	Name 7
4	BUD-001	Name 3	25/02/2024
5	BUD-001	Name 4	15/03/2024
6	BUD-002	Name 5	15/02/2024
7	BUD-002	Name 6	18/02/2024
8	BUD-002	Name 7	16/03/2024
Latest hire

Cell Formulas
Range	Formula
F2:H3	F2	=IFERROR(INDEX($B:$B,AGGREGATE(14,6,ROW($C$2:$C$8)/(($A$2:$A$8=$E2)*(TEXT($C$2:$C$8,"mmmm")=F$1)),1)),"")

yes sir it is like that, thankyou I will use XL2BB next time

but is is not working for me

 

[Peter_SSs]

but is is not working for me

Then can you post some small sample data and expected results, remembering ..

I will use XL2BB next time

 

[KAYE04]

I came up with this way, not sure if it is as comprehensive as @Peter_SSs 's version.
It requires you to have a date formatted as long month name for the column headers.

Book1
	A	B	C	D
1		control #	Name	Date Hired
2	A1	BUD-001	Angelo	2024-01-05
3	A2	BUD-001	Mark	2024-01-31
4	A3	BUD-001	Carlo	2024-02-25
5	A4	BUD-001	Alvin	2024-03-15
6
7	control#	January	February	March
8	BUD-001	Mark	Carlo	Alvin
9
10		January	February	March
11	BUD-001	Mark	Carlo	Alvin
Sheet1

Cell Formulas
Range	Formula
B10	B10	=DATE(2024,1,1)
C10:D10	C10	=EDATE(B10,1)
B11:D11	B11	=INDEX($C$2:$C$5,MATCH(MAX((MONTH($D$2:$D$5)=MONTH(B10))*($D$2:$D$5))&$A11,$D$2:$D$5&$B$2:$B$5,0))

THANKYOUUUU IT WORKS.
Thank you so much

 

[awoohaw]

Happy to help.