Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using the numeric contents of a cell to define row number | Excel Worksheet Functions | |||
Lookup - Display contents of a cell | Excel Discussion (Misc queries) | |||
Table_array lookup through a define list | Excel Worksheet Functions | |||
Cell contents as the 'lookup value' parameter in HLOOKUP function | Excel Discussion (Misc queries) | |||
Return cell contents based on conditional lookup | Excel Worksheet Functions |