Complex IF Statements

[Zmaxx]

Hi Guys

I am trying and struggling to create a nested IF statement for risk assessment that works this way:
The risk factor is calculated by multiplying the consequence(ranges from 1-7) and probability(ranges from 1-9). Based on the product of the two the values can be categorised as very low, low....high. Now the part where I'm struggling is:
#Each consequence range (1-7) has it's own criteria for whether a value is considered very low, low etc.
#I want to create an IF statement that will first check the value of consequence, then based on that value it will check the value of the product (consequence x probability) and return very low, low, torable etc
For example:
Consequence = 1, Probability = 4
Risk Score = 1×4 = 4
Risk Category = Low

Consequence = 2, Probability = 6
Risk Score = 2x6 = 12
Risk Category = Tolerable

What is the best way to approach this? Thank You

 

[AlanY]

firstly, you need to define the ranges for the very low, low & high categories.

secondly, let say score 12 = tolerable, does it matter if it's resulted from 2x6 or 3x4 etc?

 

[Zmaxx]

I managed to do it by creating a grid table with the labels and used a combination of VLOOKUP and MATCH to find the point of intersection for each possible combination. Thank You