I have an Excel sheet with 4 columns and about 1000 rows (A1:D1000 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=VLOOKU P(B$1;Data;2;FALSE);"Low"=VLOOKUP("Low";Data;4;FAL SE));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!