Max value with min collect

ateleoh

I know this is need of a combination of things, but I cant figure it out unless I use a range of cells.  I would prefer to search the collective column to perform the task.

I want to return the name of the Visit Provider who has the greatest number of SumCompSched for each Phase & Clinic.  

 

I appreciate the help in advance.

Paul Newcome

Give this a try...

 

=INDEX(CHILDREN(), MATCH(MAX(CHILDREN(SumCompSched@row)), CHILDREN(SumCompSched@row), 0))

ateleoh

Putting that in a new column gives #NO MATCH.

Paul Newcome

I didn't realize you were putting it in a different column. Give this a try...

 

=INDEX(CHILDREN([Visit Provider]@row), MATCH(MAX(CHILDREN(SumCompSched@row)), CHILDREN(SumCompSched@row), 0))

ateleoh

Thank you.  This works for finding the Visit Provider with the overall highest NumOfVisits, but I need to show who has the highest in each Clinic(Phase & Clinic).