IF and VLOOKUP functions for a matrix overview

[Marc Bucher]

I have an Excel sheet with 4 columns and about 1000 rows (A11000 named
“Dataâ€). The header row contains OrgUnitName, RiskName, ControlName adn
ControlFlag (like High/Med/Low).
A B C D
1 OrgUnitName RiskName ControlName ControlFlag
2 OU1 Risk1 Control1 Med
3 OU1 Risk1 Control1 Low
4 OU2 Risk1 Control1 High
5 OU2 Risk2 Control2 Med
6 OU3 Risk3 Control3 Low

Now, I want to create a separate sheet, where I want to built up a
two-dimensional overview with Org Units on top (e.g. Row 1) and the Risks in
column A. The cells within the matrix should get one of the following values:
0=OU has no risk allocated; 1=OU has risk allocated, but no control with a
"Low" flag; 2=OU has risk allocated and at least one ControlFlag with "Low".
What formula can I use e.g. in B2, to get to the result below.

A B C D
1 OU1 OU2 OU3
2 Risk1 2 1 0
3 Risk2 0 1 0
4 Risk3 0 0 2

I tried the following formula in B2, but it always returns #N/A:
=IF(AND($A$2=VLOOKUP($A$2;Data;1;FALSE);B$1=VLOOKUP(B$1;Data;2;FALSE);"Low"=VLOOKUP("Low";Data;4;FALSE));2;IF(AND($A$2=VLOOKUP($A$2;Data;1;FALSE);B$1=VLOOKUP(B$1;Data;2;FALSE));1;0))

Many thanks for your help in advance!

 

[JE McGimpsey]

You would be *WAY* better off to use a Pivot Table...

http://peltiertech.com/Excel/Pivots/pivotstart.htm

 

[Marc Bucher]

Hi there,
Thanks for this. I tried to use a Pivot instead of the source table, but the
problem is that I can have multiple entries for a certain risk and ou (cf.
rows 1 and 2) where I still won't get a result.

Any other idea?

 

[JE McGimpsey]

Not sure I understand the problem. A pivot table, if you use Count of
Risks, will consolidate those multiple entries. What do you mean you
"won't get a result"?

When I use the data you gave, with OrgUnitName in the column field,
RiskName in the Row field, and Count of RiskName in the Data field, I
get exactly the result table you indicate.

 

[Marc Bucher]

Hi there,

Thanks again for your feedback, but this is not what I am looking for,
because I do not want a count on risk name per OUName. Maybe the example is
not clear enough, but what I am trying to get is a formula that returns a
value of either 0, 1 or 2 in the different cells within the matrix. 0 means
OU has no risk allocated, 1 means OU has a certain risk allocated, but no
control with a "Low" flag and finally, 2 means that OU has a risk allocated
and at least one ControlFlag with "Low" (i.e. if there are five controls for
one risk in one OU and one risk has a control with a flag "Low" the value in
the cell should be a 2).

Hope it is clearer now what I am trying to get.

Thanks!