How to use LARGE function using criteria. AKA "How can I get the #1 risk based on criteria

Paul Newcome

That's how the MATCH portion of an INDEX/MATCH is supposed to work.

But when we use it to output the row number portion of the INDEX function, it stops working?

=INDEX(RISKS:RISKS, MATCH(LARGE(COLLECT([Total Risk Score]:[Total Risk Score], [Risk / Issue]:[Risk / Issue], @cell = "Risk"), 1), [Total Risk Score]:[Total Risk Score], 0))




Matthew Campbell

Good to know. Sometimes formulas just work for me and I do not understand why, ha! This was the formula we have been using and it worked! except looks can be deceiving. It only works if the hierarchy remains where Issues are mapped out above Risks, etc, etc (snippet below), BUT..

When I start mix matching issues and risks all over the place, all of a sudden it breaks, and after digging into it, I can see that the break is that when there is a DUPLICATE "total risk score", then the formula will reach for the highest on the hierarchy whether it is a risk, issue or whatever.. It's strange, very strange,

Snippet 1 of 2: This is when all Risk scores are Unique #'s (no duplicates).. it works like a charm! but this will rarely actually be the case in real life

This is what happens when duplicate numbers in the "Total risk score" start happening.. Notice the Risk Become Issues, since there are 2 duplicates, it will pull the Risk or the Issue above it in hierarchy regardless of the criteria we asked for.

Paul Newcome

In that case you would need to use some helper fields. They can be tucked at the bottom of the sheet summary fields to keep them out of the way. These fields would be just the LARGE/COLLECT portions to pull in the top three scores for each type. Then in the display field, you would use an INDEX/COLLECT along the lines of

=INDEX(COLLECT(RISKS:RISKS, [Risk / Issue]:[Risk / Issue], @cell = "Risk", [Total Risk Score]:[Total Risk Score], @cell = [Top Risk Score]#), 1)

Matthew Campbell

I knew a helper column would do it! but I was unsure of the method to get there. You are a lifesaver! I tested it and It works wonderfully. THANK YOU

I hope to see you at Engage this year because you are a legend when it comes to formulas @Paul Newcome

Paul Newcome

Happy to help. 👍️

So far the plan is to be at ENGAGE this year!