How do I find the last date on which a certain value occurred?

[ibruzzi]

Hello everyone

I would like to find the last date on which a certain value occurred. There are a bunch of people and assigned to them are letters which change as the month progress. I would like to be able to see for an example; when was the last time the letter "W" occur for Floyd Martineau and when did the letter "M" or "A" occur for him. I've tried to find a solution on the internet, but to no avail. The numbers on the 2nd row are formatted as dates. Thank you in advance for any help and here's the sheet I was working on.

Date:	07/10/2021	fri	sat	sun	mon	tue	wed	thu	fri	sat	sun	mon	tue	wed	thu	fri	sat	sun	mon	tue	wed	thu	fri	sat	sun	mon	tue	wed	thu	fri	sat	sun
S #	NAME	01	02	03	04	05	06	07	08	09	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
S-001	Clark Stubblefield	A	A	A	M	M	M	M	M	W
S-002	Royal Heckler	A	A	A	M	M	M	M	M	W
S-003	Trent Gatto	M	M	M	A	A	A	A	A	W
S-004	Mohammed Ladwig	M	M	M	A	A	A	A	A	P
S-005	Santo Bankston	M	M	M	P	P	P	P	P	P
S-006	Wally Echevarria	B	B	B	B	B	B	B	B	A
S-007	Alfonso Campuzano	W	W	W	W	W	W	W	W	A
S-008	Theron Vassar	P	P	P	P	P	P	P	P	A
S-009	Floyd Martineau	M	M	M	W	W	W	W	W	A
S-010	Rubin Sparano	A	A	A	W	W	W	W	W	W
		last "M" date			Last "W" date			last "P" date			last "A" date
	Clark Stubblefield
	Royal Heckler
	Trent Gatto
	Mohammed Ladwig
	Santo Bankston
	Wally Echevarria
	Alfonso Campuzano
	Theron Vassar
	Floyd Martineau
	Rubin Sparano

 

[jorismoerings]

Hee,

See if this helps. Does not contain all data elements you've mentioned but as the structure is similar for all other requested elements, you should get the picture else let me know.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O																	AF	AG	AH	AI	AJ	AK	AL	AM
1	date	7-10-2021																																			A	M	W
2			01	02	03	04	05	06	07	08	09	10	11	12	13																	30	31		S-001		3-10-2021	8-10-2021	9-10-2021
3	S-001		a	a	a	m	m	m	m	m	w																								S-002		3-10-2021	8-10-2021	9-10-2021
4	S-002		a	a	a	m	m	m	m	m	w																								S-003		8-10-2021	3-10-2021	9-10-2021
5	S-003		m	m	m	a	a	a	a	a	w
Sheet1

Cell Formulas
Range	Formula
AK2:AM4	AK2	=MAXIFS($C$2:$AG$2,$C3:$AG3,AK$1)

 

[ibruzzi]

Hee,

See if this helps. Does not contain all data elements you've mentioned but as the structure is similar for all other requested elements, you should get the picture else let me know.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O																	AF	AG	AH	AI	AJ	AK	AL	AM
1	date	7-10-2021																																			A	M	W
2			01	02	03	04	05	06	07	08	09	10	11	12	13																	30	31		S-001		3-10-2021	8-10-2021	9-10-2021
3	S-001		a	a	a	m	m	m	m	m	w																								S-002		3-10-2021	8-10-2021	9-10-2021
4	S-002		a	a	a	m	m	m	m	m	w																								S-003		8-10-2021	3-10-2021	9-10-2021
5	S-003		m	m	m	a	a	a	a	a	w
Sheet1

Cell Formulas
Range	Formula
AK2:AM4	AK2	=MAXIFS($C$2:$AG$2,$C3:$AG3,AK$1)

Thank you for the reply. I probably should've mentioned this, but I'm using a 2013 excel version at work and this formula doesn't seem to work because excel doesn't recognize the formula. ?

 

[Fluff]

I'm using a 2013 excel version at work

In that case you might want to update your profile to show 2013 rather than 365.
How about

=MAX(IF($C3:$AG3=AK$1,$C$2:$AG$2,""))

this will need to be confirmed with Ctrl Shift Enter, rather than just Enter.

 

[ibruzzi]

In that case you might want to update your profile to show 2013 rather than 365.
How about

=MAX(IF($C3:$AG3=AK$1,$C$2:$AG$2,""))

this will need to be confirmed with Ctrl Shift Enter, rather than just Enter.

Agreed and updated. I do use 365 on my pc and I even make up formulas not realizing I have an old *** pc at work ? That did work, so thank you very much. Much appreciated.

 

[Fluff]

Glad we could help & thanks for the feedback.