A different kind of "missing" detection formula needed...

Ok, my posts seem to have generated zero responses...maybe it was how I asked. Let me try again:

Ok I have one worksheet called Risks. New risks could be added later.

Risk Probability Impact All Apps covered? (for example)
App not Backed up Low Hi formula goes here 1
Data not Backed up Low Hi formula goes here 2
No Vendor Contract Med Low formula goes here 3
Not DR tested Low Low formula goes here 4

I have a 2nd worksheet called Applications. new apps could be added later.

Application Version Description
SMB 4.9 Central distribution
AVM 2.2 design app
FNT 8.0 renderer

I have a 3rd worksheet called Application-Risks

Application-Risk Application Risk responsible to check checkdate
SMB App
not Backed up SMB App not Backed up Joe Schmoe 2010-03-04
SMB Data
not Backed up SMB Data not Backed up Joe Schmoe 2010-03-09
SMB No
Vendor Contract SMB No Vendor Contract Jane Doe 2010-03-12
SMB Not DR
tested SMB Not DR tested Joe Schmoe 2010-03-04
FNT App
not Backed up FNT App not Backed up Frank Smith 2010-03-07
FNT Data
not Backed up FNT Data not Backed up Joe Schmoe 2010-03-04
FNT No
Vendor Contract FNT No Vendor Contract Jane Doe 2010-03-04
AVM No
Vendor Contract FNT No Vendor Contract Jane Doe 2010-03-04

the cells with formula goes here should either

1. indicate if Application-Risks have the if there is an app missing for this risk in the apps-risk table (Y/N), OR

2. indicate if the name of the first app that is missing in the App-risk table for a risk

-- whatever you find easiest. This will just tell me that there is something missing in Application-Risks, that I should fix. These lists are LONG, so this is why I need it.

For example, if the formula goes here indicates if the name of the first app that is missing in the App-risk table for a risk,then the table would read

Risk Probability Impact First App missing
App not Backed up Low Hi AVM
Data not Backed up Low Hi AVM
No Vendor Contract Med Low
Not DR tested Low Low FNT

Hope you can help.

Wow! I am soooo impressed! Seriously, THANK YOU!

I think I spoke too quickly. I realized that I only need to track risks for apps that are explicitly in-DR-scope. Essentially, there are 4 types of apps:

1) explicitly in-DR-scope "Y";
2) implicitly in-DR-scope "Implied";
3) Involved or impacted by DR "Involved";
4) not in-DR-scope "N".

I have created an example2 spreadsheet, and placed the previously suggested formula into it, and updated the headers.

I can figure out how to modify the formula in column First Missing Risk in the Apps list. However, I am do not know how to modify the formula in column First Missing App in the Risks list report on the first missing in-DR-scope "Y" app.

I have highlighted the problem cells. They show "", "d", "b". They should be "e", "", "e"

sorry, I saw that I made an error in the sheet. Here it is

First off, you replied quite speedily, sir, and I thank you.

Secondly...in a word...wow...I am blown away by that.

I'm now curious...I tried the ($B$3:$B$10="Y")* logic, but obviously not the right way, because it caused Excel to consistently blow up over here. I probably mixed types some how. What I'm curious about is the first part, where I see you have changed it, and I am wondering why?

namely instead of

=IF(COUNTIF($H$3:$H$21,$D4)=COUNTA($A$3:$A$9),""

you changed it to

=LOOKUP(REPT("Z",255),CHOOSE({1,2},""

I am sure it's an improvement over the first method, but I don't understand why you chose what you chose...care to explain? I'm interested.

Thank you again for the detailed explanation. Appreciated.