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

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"

  • 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
  • 1
    Take the formula apart and see what you get for the two different parts. – berlin Dec 20 '16 at 15:29

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.