Creating a formula to return a particular result from a Matrix Tab

[Guest]

I currently have a Matrix table below which contains the values I want
returned when the 2 columns - Impact Degree & % Customer Impacted are
populated.

% Customer Impact
Impact Degree <2% 2%-10% >10%
Nil NIL LOW MODERATE
Low LOW MODERATE HIGH
Moderate MODERATE HIGH VERY HIGH
Significant HIGH VERY HIGH EXTREME

So for example if the Impact Degree is Low and the % Impacted is >10% then
the value I want returned is HIGH.

I am currently using IF formulas adn trying to nest these without success.
I know that there are maybe lookup formulas I can use but this seems a bit
beyond me as I do not know how to reference the above table if in a different
worksheet but in the same workbook.

Thanks
Dave

 

[Guest]

Try this:

=INDEX(A1:A5,MATCH("Low",A1:A5,0),MATCH(">10%",A11,0))

 

[Guest]

Thanks for this. I maybe not have provided enough info. Understand the
formula, Where I need to use it is in workbook I am using contains 200 rows
of info of which the 2 main columns that I am populating are "% Customer
Impact & Impact Degree". Where the formula you have provided is to be used
is when onces these 2 columns are populated, then I need a third column
called "Risk Rating" to be automatically populated with the required value as
per the Risk Rating Matrix table. So it should look like something likethis:

Issue 1 % Customer Impact Impact Degree
Risk rating
Mapping <2% LOW
LOW
Communication >10% LOW
HIGH
Change 2 - 10% Moderate
Moderate

Thanks

 

[Guest]

Think there was a typo in the earlier suggestion given

=INDEX(A1:A5,...

should be:

=INDEX(A15,...

Anyway, here's a quick sample which illustrates
how the earlier suggestion could be adapted to suit:

http://cjoint.com/?lqkevJChBH
DaveAhChing_misc.xls

In D11:
=INDEX($A$2:$D$6,MATCH(C11,$A$2:$A$6,0),MATCH(B11,$A$2:$D$2,0))
Copy down as far as required to populate the "Risk rating" col

---

 

[Guest]

Thanks Max & Teethless Mama,

Your help has enabled me to get this working. It is now working perfectly.

Thanks again
Dave

 

[Max]

Always good to hear that, Dave !
Thanks for feeding back ..