Formula to calculate risk

[BravoBravoAu]

Hello,

I'm trying to use excel for a risk assessment and am struggling to get the correct formula to calculate the risk rating from concequence and likelihood.

I have a worksheet (WS1) with the following information:

<TABLE style="WIDTH: 375pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=499><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 109pt; mso-width-source: userset; mso-width-alt: 5302" span=3 width=145><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=64>

​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 109pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=145>A

​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 109pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=145>B

​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 109pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=145>C

​

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=20>1

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: white 0.5pt solid; BACKGROUND-COLOR: black; WIDTH: 109pt; BORDER-TOP: white 0.5pt solid; BORDER-RIGHT: white 0.5pt solid" class=xl68 width=145>Consequence

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: white; BACKGROUND-COLOR: black; WIDTH: 109pt; BORDER-TOP: white 0.5pt solid; BORDER-RIGHT: white 0.5pt solid" class=xl68 width=145>Likelihood

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: white; BACKGROUND-COLOR: black; WIDTH: 109pt; BORDER-TOP: white 0.5pt solid; BORDER-RIGHT: white 0.5pt solid" class=xl68 width=145>Inherent Risk Level

​

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=20>2

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 109pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 width=145>

​

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=20>3

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 109pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 width=145>

​

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=20>4

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 109pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 width=145>

​

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=20>5

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 109pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 width=145>

​

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=20>6

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 109pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 width=145>

</TD></TR></TBODY></TABLE>

Columns A and B are exclusively drop lists:
A = 'Insignificant', 'Minor', 'Moderate', Major' and 'Catastrophic'.
B = 'Rare', 'Unlikely', 'Possible', 'Likely', 'Almost Certain' and 'Ocuring Now'.

The issue that I have is automatically populating column C on the basis of information in columns A and B from a matrix in another worksheet (WS2). For instance; A is 'Insignificant' and B is 'Rare' = C is 'Low Risk'. The matrix from WS2 is as follows:

<TABLE style="WIDTH: 664pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=883><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 88pt; mso-width-source: userset; mso-width-alt: 4278" span=7 width=117><TBODY><TR style="HEIGHT: 35.1pt; mso-height-source: userset" height=46><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 35.1pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl67 height=46 width=64></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 88pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl68 width=117>A

​

</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 88pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl69 width=117>B

​

</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 88pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl69 width=117>C

​

</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 88pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl69 width=117>D

​

</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 88pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl69 width=117>E

​

</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 88pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl69 width=117>F

​

</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 88pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl69 width=117>G

​

</TD></TR><TR style="HEIGHT: 35.1pt; mso-height-source: userset" height=46><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 35.1pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl67 height=46 align=right>1

​

</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl70>Catastrophic

​

</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl70>High

​

</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl70>High

​

</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl70>Very High

​

</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl70>Extreme

​

</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl70>Extreme

​

</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl70>Extreme

​

</TD></TR><TR style="HEIGHT: 35.1pt; mso-height-source: userset" height=46><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 35.1pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl67 height=46 align=right>2

​

</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl70>Major

​

</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl70>Medium

​

</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl70>Medium

​

</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl70>High

​

</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl70>Very High

​

</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl70>Extreme

​

</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl70>Extreme

​

</TD></TR><TR style="HEIGHT: 35.1pt; mso-height-source: userset" height=46><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 35.1pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl67 height=46 align=right>3

​

</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl70>Moderate

​

</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl70>Medium

​

</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl70>Medium

​

</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl70>High

​

</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl70>High

​

</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl70>Very High

​

</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl70>Very High

​

</TD></TR><TR style="HEIGHT: 35.1pt; mso-height-source: userset" height=46><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 35.1pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl67 height=46 align=right>4

​

</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl70>Minor

​

</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl70>Low

​

</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl70>Low

​

</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl70>Medium

​

</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl70>Medium

​

</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl70>High

​

</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl70>High

​

</TD></TR><TR style="HEIGHT: 35.1pt; mso-height-source: userset" height=46><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 35.1pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl67 height=46 align=right>5

​

</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl70>Insignificant

​

</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl70>Low

​

</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl70>Low

​

</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl70>Low

​

</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl70>Medium

​

</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl70>Medium

​

</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl70>Medium

</TD></TR><TR style="HEIGHT: 35.1pt; mso-height-source: userset" height=46><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 35.1pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl67 height=46>

</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 88pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl69 width=117>

​

</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl70>Rare

​

</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl70>Unlikely

​

</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl70>Possible

​

</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl70>Likely

​

</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 88pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl69 width=117>Almost

Certain
​

</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 88pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl69 width=117>Occurring

Now​

</TD></TR></TBODY></TABLE>​

Ideally, I would like to retain the formatting from WS2 in column C, although this is only fill colour and could be done with conditional formatting if not possible in another way.

I'm confident this is possible - especially by someone with a better knowledge of excel than me - probably using a VLOOKUP formula. I'd prefer to not use code if able.

Thanks for any assistance in advance.

 

[dmadeka]

Hello,
Ideally, I would like to retain the formatting from WS2 in column C, although this is only fill colour and could be done with conditional formatting if not possible in another way.

I'm confident this is possible - especially by someone with a better knowledge of excel than me - probably using a VLOOKUP formula. I'd prefer to not use code if able.

Thanks for any assistance in advance.

You can get the value quite simply using INDEX and MATCH
Try this formula in C2

=INDEX('WS2'!$B$1:$G$5,MATCH(A2,'WS2'!$A$1:$A$5,0),MATCH(B2,'WS2'!$B$6:$G$6,0))

Not sure how you would get the color across except use VBA - might be more straightforward to copy to the conditional formatting.

HTH
dmadeka

 

[xenou]

Ditto - index is great for this. This was my mockup, showing results for Major-Possible, Minor-Almost Certain, and Catastrophic-Unlikely:

<img alt="screenshot" src="http://northernocean.net/etc/mrexcel/20111108_ws2.png" />

The Formula in C2 is an Index(Match(), Match()) structure as in the last post:
=INDEX($B$6:$G$10,MATCH(A2,$A$6:$A$10,0),MATCH(B2,$B$11:$G$11,0))

The two match arguments are used to find the row and column of the data grid, and index plots the cell location.

 

[BravoBravoAu]

Thanks both for your help. This was a pretty simple solution that I hadn't considered. Index and Match formulas worked well and conditional formatting has sorted out the remainder.

My only follow up is when I apply the formula to all of column C in WS1, the cells without information (i.e. A and B are empty) show '#N/A'. Is it possible to stop this?

Thanks again.

 

[BravoBravoAu]

Pleases ignore my last question. It probably isn't the best solution, but I used conditional formatting (white text colour) in cells with '#N/A'.