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

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

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

Try this:

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


"Dave Ah Ching" wrote:

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

Creating a formula to return a particular result from a Matrix
 

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

"Teethless mama" wrote:

Try this:

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


"Dave Ah Ching" wrote:

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

Creating a formula to return a particular result from a Matrix
 

Think there was a typo in the earlier suggestion given

=INDEX(A1:A5,...

should be:
=INDEX(A1:D5,...

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dave Ah Ching" wrote:
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

"Teethless mama" wrote:

Try this:

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


"Dave Ah Ching" wrote:

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

Creating a formula to return a particular result from a Matrix
 

Thanks Max & Teethless Mama,

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

Thanks again
Dave


"Max" wrote:

Think there was a typo in the earlier suggestion given

=INDEX(A1:A5,...

should be:
=INDEX(A1:D5,...

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dave Ah Ching" wrote:
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

"Teethless mama" wrote:

Try this:

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


"Dave Ah Ching" wrote:

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

Creating a formula to return a particular result from a Matrix
 

Always good to hear that, Dave !
Thanks for feeding back ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dave Ah Ching" wrote in message
...
Thanks Max & Teethless Mama,

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

Thanks again
Dave