Help with a Formula (find latest date)

[-emma-]

HI all,

Each day at work 3 "Prizes" are awarded. A very simple spreadsheet captures who wins what prize each day.

I have been manually capturing the number of times people have won and what date they last won. I have managed to automate the number of times won but would love some help automatically completing the last Won column if at all possible please.

In the image attached its the red date I would love help to be able to capture please.

Thank you for your time!

 

[Cubist]

Try:

Book1
	A	B	C	D	E	F	G	H	I	J
1		5/27/2024	5/28/2024	5/29/2024	5/30/2024	5/31/2024				Last Won
2	Prize1	Emma	Amy	John	Amy	Mark		Emma		5/31/2024
3	Prize2	Paul	Emma	Alice	Trev	Emma		Amy		5/30/2024
4	Prize3	John	John	Emma	Emma	Trev		John		5/29/2024
5								Mark		5/31/2024
6								Paul		5/27/2024
7								Alice		5/29/2024
8								Trev		5/31/2024
Sheet5

Cell Formulas
Range	Formula
J2:J8	J2	=MAX(IF($B$2:$F$4=H2,$B$1:$F$1))

 

[-emma-]

Try:

Book1
	A	B	C	D	E	F	G	H	I	J
1		5/27/2024	5/28/2024	5/29/2024	5/30/2024	5/31/2024				Last Won
2	Prize1	Emma	Amy	John	Amy	Mark		Emma		5/31/2024
3	Prize2	Paul	Emma	Alice	Trev	Emma		Amy		5/30/2024
4	Prize3	John	John	Emma	Emma	Trev		John		5/29/2024
5								Mark		5/31/2024
6								Paul		5/27/2024
7								Alice		5/29/2024
8								Trev		5/31/2024
Sheet5

Cell Formulas
Range	Formula
J2:J8	J2	=MAX(IF($B$2:$F$4=H2,$B$1:$F$1))

Thank you very much!

 

[Cubist]

You're welcome.