Risk Assessment Matrix

Hi all,

I'm putting together a Risk Matrix but I'm having trouble with a formula to look at two numbers and find the corresponding result in a table.
See attachment;

• Said two numbers are in cells G3 and G5
• The results is in B8 - found in the table K8-N12
• With the corresponding text in B12 - found in table P9-Q12

I'm having trouble with coming up with a formula to give me the results in B8 and B12

Any help would be greatly appreciated.


Regards,
Matt

The -1/+1, I didn't event think of that. The formula works except if the Mitigation Score is above 76, the result is N/A

Try this.

B8=IF(AND(G5>=76, G5<= 100),IF(G3=J8,K8,IF(G3=J9,K9,IF(G3=J10,K10,IF(G3=J11,K11,IF(G3=J12,K12,""))))),IF(AND(G5>=51, G5<= 75),IF(G3=J8,L8,IF(G3=J9,L9,IF(G3=J10,L10,IF(G3=J11,L11,IF(G3=J12,L12,""))))),IF(AND(G5>=26, G5<= 50),IF(G3=J8,M8,IF(G3=J9,M9,IF(G3=J10,M10,IF(G3=J11,M11,IF(G3=J12,M12,""))))),IF(AND(G5>=0, G5<= 25),IF(G3=J8,N8,IF(G3=J9,N9,IF(G3=J10,N10,IF(G3=J11,N11,IF(G3=J12,N12,""))))),""))))


B12=IF(B8=P9,Q9,IF(B8=P10,Q10,IF(B8=P11,Q11,IF(B8=P12,Q12,""))))

Nailed it! Cheers!!!

Please add reputation and mark it as solved.