Formula to calculate risk

B

BravoBravoAu

Board Regular
Joined
Nov 8, 2011
Messages
63
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
BravoBravoAu said:
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.
Click to expand...

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
 
Upvote 0
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
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'.
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Solved
How to put a space in this table in VBA?
Replies
1
Views
315
VBAEXCELNew
V
G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
820
gvsumich
G
A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C
E
  • Question
Adding a custom message.
Replies
1
Views
426
Micron
M

Forum statistics

Threads
1,214,800
Messages
6,121,641
Members
449,044
Latest member
hherna01

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top