| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex Look-up
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? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Complex Look-up
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","")) "Vic" wrote: 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? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Complex Look-up
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")) "joemeshuggah" wrote: 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","")) "Vic" wrote: 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? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Complex Look-up
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" wrote: 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")) "joemeshuggah" wrote: 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","")) "Vic" wrote: 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? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Complex Look-up
=IF((SUMPRODUCT(--(BM!$D$2:$D$500=D2),--(BM!$G$2:$G$500<"")))0,"","Yes")
"Vic" wrote: 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" wrote: 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")) "joemeshuggah" wrote: 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","")) "Vic" wrote: 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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| complex color fill conditions- if statements or complex formula? | Excel Discussion (Misc queries) | |||
| Complex If then? | Excel Worksheet Functions | |||
| Complex SUM | Excel Worksheet Functions | |||
| Complex sum | Excel Discussion (Misc queries) | |||
| Complex Index Match Help (or at least complex to me) | Excel Discussion (Misc queries) | |||
Linear Mode
