Complex Look-up

[Vic]

I need to put "Yes" into N2 if my account in D2 is among accounts from sheet
BM cells ranging from D2 thru D500. This formula works:
=IF(COUNTIF(BM!$D$2:$D$500,D2)>0,"Yes","")
Now, I need to expand this formula. Once the D cell from BM sheet matched
with N, I need to check that the corresponding G in BM sheet is blank. If
this is the case then I should put "Yes" in N. How can this be done?

 

[joemeshuggah]

im not sure i follow, but maybe this would work...

=IF(ISNA(VLOOKUP(D3,BM!D:G,4,FALSE)),"",IF(VLOOKUP(D3,BM!D:G,4,FALSE)="","Yes",""))

 

[joemeshuggah]

or...

=IF(ISNA(VLOOKUP(D2,BM!$D$2:$G$500,4,FALSE)),"",IF(VLOOKUP(D2,BM!$D$2:$G$500,4,FALSE)="","Yes - Blank","Yes"))

 

[Vic]

Here is the explanation in English:
My summary sheet has a list of about 500 accounts (column N). I need to find
accounts with all issues resolved. In order to do this, for each account I
need to look in BM sheet (Problematic accounts) to see if that account is in
that list in column D. Since account may have more than 1 problem, it is
normal for that account to appear several times in BM sheet. The account is
considered clear of all problems if BM sheet has all occurences of that
account in column D with corresponding cell G being blank. Cell G in BM sheet
has the date when that specific problem was tracked. This field is turned
blank when the problem gets resolved.
Can you please help me with the formula that I need to put in N to find all
clean accounts?
Thank you.

 

[joemeshuggah]

=IF((SUMPRODUCT(--(BM!$D$2:$D$500=D2),--(BM!$G$2:$G$500<>"")))>0,"","Yes")