Hi
Please refer to the attached spreadsheet.
Sheet "RiskData" A1:F6 is the data to be used.
Sheet "Risks": Column G uses a dropdoen list based on "RisksData"A2:A6
Sheet "Risks": Column H uses a dropdoen list based on "RisksData"B1:F1
The problem I face is:
Sheet "Risks": Column I needs to return the value from "RisksData"B2:F6 based on the combination selected in "Risks": Column G and "Risks": Column H, with "Risks": Column I conditional formatted to the colour matching the value returned from "RisksData"B2:F6
A solution and advice would be very appreciated.
Thank you, Gary
Hello Gary,
I used an INDEX & MATCH formula to get the value for cell I2. Use conditional formatting on the cell to catch the correct colour based on the value you get.
=INDEX(RiskData!$B$2:$F$6,MATCH([@[INHERENT Likelihood]],likelihood,0),MATCH([@[INHERENT Impact]],Impact,0))
Hello Anders
Monday morning, just back at work.
Thanks very much for your response, and it works great - thank you.
Much easier than the way I was trying to achieve the same.
Regards, Gary
Hi Anders
Further to your formula:
I added an IFERROR function around your formula to handle the #N/A in the cells to tidy up the look of the spreadsheet.
Gary
Hello Gary,
Yes, it is always good to include some error handling functions, just in case. Great that the formula worked fine for you.
