0

I'm trying to create a formula for conditional formatting that will identify two words and create an output of a word.

See example below:

Excel Cells

Right now I'm having to manually put in the Risk Type. I want it to output the risk type based on the likelihood and impact.

Risk Chart

bummi
  • 1,703
  • 4
  • 16
  • 28
Morgan
  • 1
  • 1

1 Answers1

0

Personally, I'd create the risk chart as a table and then use index match pairs to find the row and column to locate the result you are seeking.

=index(RISK_TABLE_RANGE,MATCH(Likelihood_Cell,Likelihood_Range_RISKTABLE,0),
 MATCH(Risk_Cell,Impact_Range_RISKTABLE,0))

Essentially, you have the entire RISK_TABLE as one range and two additional ranges Likelihood_Range and Impact_Range which are the header/index for your risk table. You match on the two ranges and you get the cell coordinates for the RISK Level which appears in the square.

Think of it as a game of battleship where you ask "what row does very unlikely appear" and then "what column does major appear"

berlin
  • 41
  • 8
  • I put in the formula you posted and linked it to my risk table, but it did not place in Risk type after I filled in the Likelihood and Impact columns. Not sure why? – Morgan Dec 19 '16 at 22:24
  • You need to define three different named ranges: – berlin Dec 19 '16 at 22:58
  • You need to define three different named ranges: 1. the entire RISK_TABLE 2. the headers for the Likelihood_Range 3. the headers for the Impact_Range – berlin Dec 19 '16 at 23:00
  • Oh wow, I'm not sure how to do that. – Morgan Dec 19 '16 at 23:06
  • Go into Formulas -> Define Name – berlin Dec 19 '16 at 23:08
  • Go into Formulas -> Define Name In my example the named ranges are called RISK_TABLE Likelihood_Range and Impact_Range You can define the areas that they cover and then it should work. – berlin Dec 19 '16 at 23:10
  • Thank you. I got it to go through but it still doesn't do the function I'm looking for. The Risk field doesn't automatically fill in with a word, even after I put the Likelihood and Impact in. – Morgan Dec 19 '16 at 23:22
  • 1
    Take the formula apart and see what you get for the two different parts. – berlin Dec 20 '16 at 15:29