Working with a table and 2 drop down lists

QuestionEdit

I am setting up a risk spreadsheet using a standard rating table.

Is there a way I can have 2 drop down lists "Likelihood" and "consequence" which, when depending on the selection, populate a rating column with either, "Extreme", "High", "Medium", or "low", based on a table?

The table is on another worksheet and would like something like this: Insignificant Minor Moderate Major Severe Almost Certain Medium High Extreme Extreme Extreme Likely Medium High High Extreme Extreme Possible Low Medium High Extreme Extreme Unlikely Low Low Medium High Extreme Rare Low Low Medium High High

So, if "Almost Certain" and "Minor" is selected, then "High" is automatically displayed.

AND is there a simple way to have the cell background color change also depending on the result?

Thank you Renata

AnswerEdit

Renata:

The best way to answer your question is by example. Thus, I have uploaded a sample file here:

http://home.swbell.net/nate-sus/excel/ForRenata.xls

1) You will want to create your drop-down lists using Data Validation (a menu option in Excel). 2) You will want to use the INDEX and MATCH functions to do a double-lookup of your table. These functions are semi-complex, so please use my example to better understand what the function is doing. 3) You will want to use Conditional Formatting (another menu option in Excel) to format the cell. Older versions ofonly allow 3 conditional formats (so 4 total considering the default color). This appears to work for your situation; however, if you add another severity rating then you will run into issues (unless you have2007 or later and then the sky is pretty much the limit).

Please let me know if you have any additional questions!