COLLECT and MAX
I have been trying to find the right combination of formulas to do this in place. I am trying to report the name of a contact in the same row as a max value. I have attached an example photo to help describe my situation
In the scenario, I am trying to look up the maximum number in the Parts Column and return the managers name on that row. The formula will be in a sheet summary. In the example above, the max value would be 300 and the formula should return the contact "Corey Gill"
Any help will be appreciated. I think I am making the solution more complicated than it needs to be.
Best Answer

@COREY GILL I believe that combining INDEX, MATCH and MAX will get you what you're looking for. This sample formula is using INDEX to evaluate the Manager column, and combining MATCH(MAX) to determine the highest Parts value to return as the row to return for the INDEX formula, which should get you exactly what you're looking for. There are probably other ways to do this, but this should work.
=INDEX(Manager:Manager, MATCH(MAX(Parts:Parts), Parts:Parts))
Hope that helps!
Alex
Answers

@COREY GILL I believe that combining INDEX, MATCH and MAX will get you what you're looking for. This sample formula is using INDEX to evaluate the Manager column, and combining MATCH(MAX) to determine the highest Parts value to return as the row to return for the INDEX formula, which should get you exactly what you're looking for. There are probably other ways to do this, but this should work.
=INDEX(Manager:Manager, MATCH(MAX(Parts:Parts), Parts:Parts))
Hope that helps!
Alex

You nailed it! Thanks

@Alexander Ford Since you we're so quick on the last one, maybe you can help me take my formulas a bit further. Below is a similar example but with a corresponding date for each entry.
I have created a formula to find the 30 day rolling average of the number of parts. It looks like this:
=AVG(COLLECT(Parts:Parts, Date:Date, >=TODAY(30), Parts:Parts, Parts:Parts >= 1))
What I would like to have to complement this 30 day average is to identify the Manager that had the highest number of parts in the last 30 days. In the example above, it would be Corey G as the manager with the highest number of parts in the last 30 days. Any insight?

This one took a little hacking but I believe it should work. For the MATCH function, it seems you have to include the 0 at the end of the formula for nonsorted search type. In retrospect, I'm not sure why this wasn't required for the previous formula in this thread to function correctly, but someone much smarter than myself might be able to provide some feedback on that... :)
=INDEX(Manager:Manager, MATCH(MAX(COLLECT(Parts:Parts, Date:Date, >TODAY(30))), Parts:Parts, 0))
Thanks,
Alex
Help Article Resources
Categories
Check out the Formula Handbook template!