Index, Match, Max, Collect

Mary_B

I am trying to use the following formula to identify the name of the last Manager in Training (MIT) to graduate their training program with a certain Mentor, but wherever there are instances of MITs having the same graduation date, the formula is returning the wrong MIT for the given Mentor, and I cannot figure out why. Any help would be greatly appreciated. 

(INDEX({MIT Grads}, MATCH(MAX(COLLECT({Grad Date 2}, {Mentor}, [Mentor Contact]@row)), {Grad Date 2}, 0))

Paul Newcome

@Mary_B I would suggest inserting a helper date column onto your Mentor List Sheet. Move your MAX/COLLECT for the dates over to there.

Then you can use an INDEX/COLLECT like so...

=IFERROR(INDEX(COLLECT({MIT Grads}, {Grad Date 2}, [Date Helper]@row, {Mentor}, [Mentor Contact]@row), 1), "")

Bassam Khalil

Hi @Mary_B

Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

Mary_B

Thank you so much for your help!

Paul Newcome

Are you able to provide some screenshots with mock data entered that shows the issue? It is hard to tell if there are multiples of the same name, and I do not see duplicate dates anywhere to get an idea of exactly what is going on vs what should be happening.

Mary_B

Thanks for responding @Paul Newcome!

Please see mock sheets below showing the following formula error:

According to the MIT Training Roster, the last MIT that Silvia Smith mentored was Jennifer Lynn; however, because Jennifer has the same Graduation Date as Julie Smith, the formula in the Mentor List is returning Julie Smith as the last MIT mentored by Silvia, which is not accurate (Julie was mentored by James Smith).

Thanks so much for your help!

Paul Newcome

@Mary_B I would suggest inserting a helper date column onto your Mentor List Sheet. Move your MAX/COLLECT for the dates over to there.

Then you can use an INDEX/COLLECT like so...

=IFERROR(INDEX(COLLECT({MIT Grads}, {Grad Date 2}, [Date Helper]@row, {Mentor}, [Mentor Contact]@row), 1), "")

Mary_B

@Paul Newcome Worked like a charm!! Thanks so much!!

Paul Newcome

Happy to help. 👍️