Look up a value from a table, adding the text and changing the cell background colour

Hi

I am doing a Risk Register. I have one worksheet (Sheet 1) that contains the Risk Scoring Guide, on the second worksheet I have created the Risk Register "Generic Company Risks".

If you go to Column H, you can select the Propbability (e.g Rare, unlikely, Moderate), which matches the table in sheet one.

If you go to Column I, you can select the Impact (e.g. Insignificant, Minor, Significant....) which also matches the table in sheet one.

What I am then trying to do in Column 'J" which is "total Risk score" is for it to automatically provide the score and colour scheme from worksheet 1.

So say if I select in Column H "Moderate 3" and then select "Major 4", I would then expect Column J to automatically look up the table in worksheet 1 with these two values and then automatically change the cell accordingly to match, so the Cell for this example would be highlighted in yellow and would say "12 High" and the background of that cell would turn to yellow. So each time you change the options in columns H and I, column J will change accordingly and automatically to match the table in worksheet 1.

I would really appreciate someones help here, I don't know whether a lookup table would work or whether I need to use "If' statements or a combination of both?

You can put this formula in J3:

=IF(OR(H3="",I3=""),"",INDEX('Risk Scoring Guide'!$C$5:$G$9,MATCH(H3,'Risk Scoring Guide'!$B$5:$B$9,0),MATCH(I3,'Risk Scoring Guide'!$C$4:$G$4,0)))

If you want the colours to change, you will need to use Conditional Formatting with about 9 different rules to cover all colours. Copy the formula down as required.

Hope this helps.

Pete