Return a cell when one column matches another within an IF statement.
I've been struggling with how to return a specific value from two columns.
We have created a risk register, and we want to show the overall risk rating for the project. The Overall risk is simply the risk of the highest ranked risk regardless of count.
Initially, we are just using an IF statement, with a HAS on the Risk Ranking Post Treatment Column. Due to ordering, this will always return the highest risk. This is working fine (Formula below).
What I would like to do now is only return the risk ranking, if the status is open. The next column is called Status, and each risk is open or closed. I'm banging my head against a wall. I've tried IF statements with HAS and ANDs, but that isn't returning what I would expect. I've used combinations of INDEX, COLLECT but can't seem to make that work.
I'm sure that I am missing something simple, so any help would be appreciated.
=IF(HAS([Risk Ranking - Post Treatment]:[Risk Ranking - Post Treatment], "Extreme"), "Extreme", IF(HAS([Risk Ranking - Post Treatment]:[Risk Ranking - Post Treatment], "High"), "High", IF(HAS([Risk Ranking - Post Treatment]:[Risk Ranking - Post Treatment], "Medium"), "Medium", "Low")))
Help Article Resources
Check out the Formula Handbook template!