3 text lookup tables use to define contents of a cell
 

Hi I've 3 lookup tables that I want to use to define what the contents of a
ceel should be. Tables For example: I need to compare the 1st two tables to
automatically define the risk level contained in the 3rd table.
Likelihood Consequences Risk level
Almost certain Insignificant Low
Likely Minor Medium
Possible Moderate High
Unlikely Major Extreme
Rare Catastrophic
Any suggestions or help would be fantastic, thanks

3 text lookup tables use to define contents of a cell
 

McNic --
How about assigning numerical values to the first two tables, and
manipulating them somehow (multiplying or adding) to get a numerical risk
level. So the different 'likelihood' states get values 1-5 in order of
increasing likelihood, and the different 'consequences' states get the same.
Then you could either just multiply, or get fancy with nexted 'IF'
statements, a la:

A B C D E
1 Likelihood # Conseq. # Risk
2 Likely 4 Insignificant 1 see
formula below

E2 formula

=if(b2*d220,"Extreme",if(b2*d215,"High", etc etc

HTH

"McNic" wrote:

Hi I've 3 lookup tables that I want to use to define what the contents of a
ceel should be. Tables For example: I need to compare the 1st two tables to
automatically define the risk level contained in the 3rd table.
Likelihood Consequences Risk level
Almost certain Insignificant Low
Likely Minor Medium
Possible Moderate High
Unlikely Major Extreme
Rare Catastrophic
Any suggestions or help would be fantastic, thanks

3 text lookup tables use to define contents of a cell
 

I need to have text values but thanks.

I created a further table with the contents from the first two table merged
into this table. I was then able to use the following which works perfectly:
=VLOOKUP($E8&$F8,R4:S28,2,FALSE)

Thanks so much for your quick reply :)

"pdberger" wrote:

McNic --
How about assigning numerical values to the first two tables, and
manipulating them somehow (multiplying or adding) to get a numerical risk
level. So the different 'likelihood' states get values 1-5 in order of
increasing likelihood, and the different 'consequences' states get the same.
Then you could either just multiply, or get fancy with nexted 'IF'
statements, a la:

A B C D E
1 Likelihood # Conseq. # Risk
2 Likely 4 Insignificant 1 see
formula below

E2 formula

=if(b2*d220,"Extreme",if(b2*d215,"High", etc etc

HTH

"McNic" wrote:

Hi I've 3 lookup tables that I want to use to define what the contents of a
ceel should be. Tables For example: I need to compare the 1st two tables to
automatically define the risk level contained in the 3rd table.
Likelihood Consequences Risk level
Almost certain Insignificant Low
Likely Minor Medium
Possible Moderate High
Unlikely Major Extreme
Rare Catastrophic
Any suggestions or help would be fantastic, thanks

3 text lookup tables use to define contents of a cell
 

I have found another formula that works hopefully it may help others; see below
=IF(OR(AND(E8="Almost
certain",OR(F8="moderate",F8="major",F8="catastrop hic")),AND(E8="Likley",OR(F8="major",F8="catastrop hic")),AND(E8="Possible",OR(F8="major",F8="catastr ophic")),AND(E8="Unlikley",F8="catastrophic")),"EX TREME",IF(OR(AND(E8="Almost
certain",OR(F8="insignificant",F8="minor")),AND(E8 ="Likley",OR(F8="minor",F8="moderate")),AND(E8="Po ssible",F8="moderate"),AND(E8="Unlikley",F8="major "),AND(E8="Rare",OR(F8="major",F8="catastrophic")) ),"HIGH",IF(OR(AND(E8="Likley",F8="insignificant") ,AND(E8="Possible",F8="minor"),AND(E8="Unlikley",F 8="moderate"),AND(E8="Rare",F8="moderate")),"MEDIU M",IF(OR(AND(E8="Possible",F8="insignificant"),AND (E8="Unlikley",OR(F8="insignificant",F8="minor")), AND(E8="Rare",OR(F8="insignificant",F8="minor"))), "LOW",""))))

"McNic" wrote:

I need to have text values but thanks.

I created a further table with the contents from the first two table merged
into this table. I was then able to use the following which works perfectly:
=VLOOKUP($E8&$F8,R4:S28,2,FALSE)

Thanks so much for your quick reply :)

"pdberger" wrote:

McNic --
How about assigning numerical values to the first two tables, and
manipulating them somehow (multiplying or adding) to get a numerical risk
level. So the different 'likelihood' states get values 1-5 in order of
increasing likelihood, and the different 'consequences' states get the same.
Then you could either just multiply, or get fancy with nexted 'IF'
statements, a la:

A B C D E
1 Likelihood # Conseq. # Risk
2 Likely 4 Insignificant 1 see
formula below

E2 formula

=if(b2*d220,"Extreme",if(b2*d215,"High", etc etc

HTH

"McNic" wrote:

Hi I've 3 lookup tables that I want to use to define what the contents of a
ceel should be. Tables For example: I need to compare the 1st two tables to
automatically define the risk level contained in the 3rd table.
Likelihood Consequences Risk level
Almost certain Insignificant Low
Likely Minor Medium
Possible Moderate High
Unlikely Major Extreme
Rare Catastrophic
Any suggestions or help would be fantastic, thanks