Return a cell when one column matches another within an IF statement.

Nick Horton

Morning,

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.

Thanks

Nick

=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")))

Paul Newcome

I would suggest replacing your HAS with a CONTAINS and then replacing the range with a text string that pulls together all of the risks that have a status of open.

To get a text string that contains all of them for open...

=JOIN(COLLECT([Risk Ranking - Post Treatment]:[Risk Ranking - Post Treatment], Status:Status, "Open"), ",")

Now we nest that in the CONTAINS function to search for "Extreme".

=CONTAINS(join_function, "Extreme")

=CONTAINS(JOIN(COLLECT([Risk Ranking - Post Treatment]:[Risk Ranking - Post Treatment], Status:Status, "Open"), ","), "Extreme")

and put that in your IF...

=IF(CONTAINS(join_function, "Extreme"), "Extreme", .................

=IF(CONTAINS(JOIN(COLLECT([Risk Ranking - Post Treatment]:[Risk Ranking - Post Treatment], Status:Status, "Open"), ","), "Extreme"), "Extreme", .................

If you continue with the pattern of CONTAINS/JOIN/COLLECT, you should be up and running.

Nick Horton

Morning Paul,

Thanks loads for this, I love the approach that you've taken.

I can't quite get it working, and I am sure that there is something that I have done wrong.

In the below, even when there is an extreme event it is returning XXX (the Else statement). You can see the two data columns in the image below.

=IF(CONTAINS(JOIN(COLLECT([Risk Ranking - Post Treatment]:[Risk Ranking - Post Treatment], Status:Status, "open"), ", "), "Extreme"), "Extreme", "XXX")

I've split the Join Collect out, and that is working as expected

=JOIN(COLLECT([Risk Ranking - Post Treatment]:[Risk Ranking - Post Treatment], Status:Status, "open"), ", ")

Returns = High, Extreme, Low

When looking at the formula, (The cursor is sitting on the first "extreme") it looks like it could be missing a range, or this could be my lack of understanding of how the Contains formula works.

I tweaked the formula to the below (re-ordering the Contains formula) and it appears to be returning the expected values. Does this make sense?

=IF(CONTAINS("Extreme", (JOIN(COLLECT([Risk Ranking - Post Treatment]:[Risk Ranking - Post Treatment], Status:Status, "open"), ", "))), "Extreme", "XXX")

THANKS again, as I wouldn't have gotten close without your help.

Paul Newcome

My apologies. I mixed up the order of the CONTAINS function. The search criteria should come first followed by the range. You are correct with your second one. Sorry about that.

Nick Horton

Hey Paul,

Please don't apologise! I wouldn't have gotten anywhere without your help!

I just wanted to make sure that I wasn't going mad, or had introduced other issues.

Thanks again

Cheers

Nick