Alternative for using more nested "'IF"functions

**Henryoung** · 04-16-2013, 06:13 PM

I have a matrix measuring the likelihood of a risk occurring and the likely impact. The Ranking are "Low", "Medium" and "High". I can't find figure out the formula that can comprehensively capture it in excel so that when I input the rankings of the two variable, the final ranking will be generated automatically. I have tried to use the IF(AND..... function but it can't capture the whole matrix. I have attached the template i am designing

I Will appreciate your help

**daddylonglegs** · 04-16-2013, 06:19 PM

Hello Henryoung, try this formula

=INDEX($B$12:$D$14,MATCH(I6,$A$12:$A$14,0),MATCH(J6,$B$11:$D$11,0))

You have a "trailing space" in B11, you should remove that otherwise the formula won't work

**Henryoung** · 04-18-2013, 04:39 PM

Thank you so much. That works well, however I am wondering whether there is a function that can return the same results without referencing to the matrix itself. Just like how the IF function can be used.

Will appreciate the feedback from you guys.

**daddylonglegs** · 04-18-2013, 05:46 PM

OK, you can use INDEX/MATCH like this

=INDEX({"Medium","High","High";"Low","Medium","High";"Low","Low","Medium"},MATCH(I6,{"High";"Medium";"Low"},0),MATCH(J6,{"Low","Medium","High"},0))

**Jonmo1** · 04-18-2013, 07:30 PM

Instead of a whole matrix, you can just have 3 cells with Low Medium and High

B17:B19 for example.

=LOOKUP(MATCH(I6,B17:B19,0)+MATCH(J6,B17:B19,0),{1,4,5},B17:B19)

**Henryoung** · 04-19-2013, 10:55 AM

Originally Posted by daddylonglegs

OK, you can use INDEX/MATCH like this

=INDEX({"Medium","High","High";"Low","Medium","High";"Low","Low","Medium"},MATCH(I6,{"High";"Medium";"Low"},0),MATCH(J6,{"Low","Medium","High"},0))

Thanks. Its has worked perfectly. No qualms.

Problem solved