Stopping a MAX function after one result.

I'm using a MAX function with 2 ranges =MAX(IF(ActDName=$A3,ActDDate)). Which I'm using to bring back the most recent date where a development plan is in place. In essence, it looks up a person...

excel

Formulas and Functions

Detlef_Lewin

Oct 03, 2018

Your Excel version doesn't support MAXIFS().

Use AGGREGATE() instead.

=IF(COUNTIFS(ActDName,A3,ActionDevelopment!$F$2:$F$14,"Yes"),"",AGGREGATE(14,6,ActionDevelopment!$D$2:$D$14/(ActDName=A3),1))

Dale Lori

Copper Contributor

Oct 08, 2018

Still a problem. It works, but doesn't match against the names e.g. it is bringing back the values from the ActionDevelopment sheet but not against the right name? It just brings back the dates I've entered (where I've entered 5 dates) against the top 5 people not the actual person they've been applied against. If I enter dates for the top 5 people, it works, but if I enter the top person, then 2 from the middle and 2 from the bottom, it lists those 5 dates against the top 5 people, not the correct names.

Could I ask what the 14 and 6 mean in the Aggregate formula, as Aggregate is not a formula I've used before.

NB. I can't send you my actual sheet as it has some customer data in it.

Dale Lori

Copper Contributor

Oct 09, 2018

I think I've sorted it. Thank you for all your help Detlef Lewin, it's very much appreciated.

Forum Discussion

Stopping a MAX function after one result.

Resources