Need urgent assistance with TEXT formulas

  • Dear experts,


    I have attached an excel sheet where I need following help very urgently


    If column G (Probability) contains text MODERATE or RARE or LIKELY or UNLIKELY

    AND

    If column H (Impact) contains text MAJOR or TRIVIAL or MODERATE or EXTREME

    THEN

    Column I (Rating) should return the text of my choice (Either LOW or MEDIUM or HIGH)


    Can you urgently assist?

  • Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a selection in Column G or H.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Try the attached file.

    Files

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • You could also do this with a formula in I2 copied down

    =IFERROR(INDEX('Risk Rating'!$D$5:$H$9,MATCH(G2,'Risk Rating'!$C$5:$C$9,0),MATCH(H2,'Risk Rating'!$D$4:$H$4,0)),"")

  • Hi Fluff13. I need your help again please. The Risk rating has been changed. I am attaching the file again. Can you help me fix the formula?

    I want to rearrange the ratings after linking it to the second table (which has risk numbers). I want to remove linking from the first table

  • Firstly your drop downs need to exactly match the criteria in the chart.

    Secondly, whilst in this situation the merged cells are probably not a problem, I never touch merged cells.

  • Firstly your drop downs need to exactly match the criteria in the chart.

    Secondly, whilst in this situation the merged cells are probably not a problem, I never touch merged cells.

    Thanks Fluff13. I am resending the file to you after making necessary changes in drop downs in another 5-10 minutes. Please help

  • I actually want the risk rating score to come out in the Risk Rating column (Column I) which is not happening.


    For instance, for the first Risk, if probability is 'almost certain" and Impact is "Major", the score should be 20