Having issues with a IF(MAX(INDEX formula returning Yes or No

[mattbird]

Hi,

I'm trying to use a ID number to return the most current training date for a staff member and whether its in date or out of date, using the following formulas:

Formula in Colum Q: {=IF(MAX(INDEX((S2=$S$2:$S$8)*$P$2:$P$8,))=S2,"Yes","No")} - Keeps returning 'No' for everything

Formula in Colum R: =IF(P2 < TODAY()-365,"Out of Date","In Date") - Seems to work, but if there is a better formula please let me know

but it keeps returning 'No' for everything?

Example of what it should do:

	P	Q	R	S
1	Training Date	Formula column/Results	In Date / Out of Date	Staff ID
2	22/02/2021	No	Out of Date	22222222
3	15/09/2021	Yes	Out of Date	22222222
4	28/08/2020	No	Out of Date	22222222
5	15/09/2021	Yes	Out of Date	22222222
6	22/10/2021	No	Out of Date	66666666
7	22/09/2023	Yes	In Date	66666666
8	07/11/2020	Yes	Out of Date	33333333

 

[RoryA]

MAX is returning the highest date but you're comparing that to column S which is the ID number, so none of them will match.

 

[mattbird]

MAX is returning the highest date but you're comparing that to column S which is the ID number, so none of them will match.

Hi Rory,

I've been looking at the formula for ages, don't know how I didn't see that .

Thanks for pointing that out to me.

 

[RoryA]

Glad we could help.

Please note that I have changed the marked solution as your post wasn't actually the answer.