Help Please INDEX & MATCH functions

**christine.golledge** · 02-01-2012, 06:30 PM

I have created a Hazard Analysis table which I would like to be able to put in a Consequence rating (J1), Probability rating (J2) and return a Risk Score (J3) which will then convert into a Risk Rating (J4). Can anyone please help me as I am confused.

Many many thanks

**rylo** · 02-01-2012, 06:42 PM

Hi

J3: =INDEX(B2:G7,MATCH(J1,B2:B7),MATCH(J2,B2:G2))

In the range M3:N7 enter the following
0,High
7,Moderate
14,Low
20, Minimal

J4: =VLOOKUP(J3,M3:N6,2)

Or if you don't want to have a lookup table then
J4: =VLOOKUP(J3,{0,"High";7,"Moderate";14,"Low";20,"Minimal"},2)

HTH

rylo