Hi all.. me again, Could anyone help me solve and understand formula issue

chrisfinlay

I am creating a risk and issues log within Smartsheet attempted a formula to calculate an automated response based of my matrix:

However, after several attempts to even get one line working, nothing will work for me. Could someone please offer me some help/advice?

I have tried the following:

=IF(AND(Consequence@row="Insignificant", Likelihood@row="Unlikely", "Low")))

=IF(AND(OR(Consequence3 = "Insignificant", Likelihood3 = "Unlikely", [Inherent Risk Rating]3, "Low"))

=IF(AND(OR(Consequence4 = "Insignificant", Likelihood4 = "Unlikely", [Inherent Risk Rating]4 = "Low")))

None will work, they provide me with the following responses:

I hope to have a formula that will look somewhat like the following formula (i found it online):

=IF(OR(AND(Probability@row = "low", Impact@row = "low"),AND(Impact@row = "med", Probability@row = "low"),AND(Probability@row = "med", Impact@row = "low")),"Low",IF(OR(AND(Probability@row = "med", Impact@row = "med"),AND(Impact@row = "high", Probability@row = "low"),AND(Probability@row = "high", Impact@row = "low")),"Medium",IF(OR(AND(Probability@row = "low", Impact@row = "critical"),AND(Impact@row = "high", Probability@row = "med"),AND(Probability@row = "high", Impact@row = "med")),"High","Critical")))

ker9

Hi,

The post link below has the answer towards the bottom and includes great supporting text and graphics:

https://community.smartsheet.com/discussion/86632/create-report-with-text-from-multiple-columns-but-stacked-vertically-in-rows#latest

Using "TestMatch" as the name of the lookup sheet where the matrix lives:

=INDEX({TestMatch ALL}, MATCH(Probability@row, {TestMatch Probability}, 0), MATCH(Impact@row, {TestMatch TopRow}, 0))