nested ifs error

[InterMed]

I can't seem to figure out why my nested if with AND formula isn't working. I'll explain it in words and hopefully you can help me out:

1) If value in cell A12 matches value in the vlookup formula AND
2) each of the values in the following cells C12,D12,H12,I12,AC12,AE12,AF12,BD12,BF12,CB12,CX12 is greater than zero, then return the value YES,
3) If If value in cell A12 matches value in the next vlookup formula AND
4) each of the values in the following cells C12,D12,O12,P12,AL12,AM12,AN12,AO12,BL12,BM12,BN12,CI12,CJ12,DE12 is greater than zero, then return the value YES
5) same goes for the next set of formulas..
.
.
8) Otherwise return blank or NO

Note: if (2) returns Yes, (4) won't return yes because A12 won't match the new vlookup in (3)

if(and((vlookup($A12,'Group Info'!$A$14:$D$1026,4,0)=$CW$8),(C12,D12,H12,I12,AC12,AE12,AF12,BD12,BF12,CB12,CX12)>0),"Yes",if(and((vlookup($A12,'Group Info'!$A$14:$D$1026,4,0)=$DD$8),(C12,D12,O12,P12,AL12,AM12,AN12,AO12,BL12,BM12,BN12,CI12,CJ12,DE12)>0),"Yes",if(and((vlookup($A12,'Group Info'!$A$14:$D$1026,4,0)=$dk$8,(C12,D12,V12,AU12,AV12,AW12,AX12,BT12,BU12,BV12,CP12,CQ12,DL12)>0),"YES",""))))


I keep getting #ERROR! message.

Thanks in advance!

[Gregb11]

It's best to always attach a sample file. Please see the yellow banner at the top of the page.

Also, for your version, when Excel is open, you should be able to click on "File", then "Account" and it should tell you. There are other ways depending on your version so it may be different for you.

Lastly, for your IF statement, for the AND portion, you need to put the expression for EACH cell, in other words:

C12>0,D12>0,H12>0, etc.

[AliGW]

Administrative Note:

Members will tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.

[davsth]

I agree with the above a sheet would be much easier but (C12,D12,H12,I12,AC12,AE12,AF12,BD12,BF12,CB12,CX12)>0 is likely to error.


min(C12,D12,H12,I12,AC12,AE12,AF12,BD12,BF12,CB12,CX12)>0 may be a replacement that works and perhaps slightly shorter than Greg's suggestion, but he correctly identifies an error

Also will the lookup always find something even if it is not equal? or will it find nothing and produce an error?
What is the value being matched is it a number or could it be text?

[InterMed]

Thanks gregb11, aligw and davsth,
I changed the formula as suggested to this now:
if(and(vlookup(A11,'Group Info'!$A$14:$D$1026,4,0)=$CW$8),(C11>0,D11>0,H11>0,I11>0,AC11>0,AE11>0,AF11>0,BD11>0,BF11>0,CB11>0,CX11>0)),"Yes",if(and((vlookup(A11,'Group Info'!$A$14:$D$1026,4,0)=$DD$8),(C11>0,D11>0,O11>0,P11>0,AL11>0,AM11>0,AN11>0,AO11>0,BL11>0,BM11>0,BN11>0,CI11>0,CJ11>0,DE11>0),"Yes",if(and((vlookup(A11,'Group Info'!$A$14:$D$1026,4,0)=$dk$8,(C11>0,D11>0,V11>0,AU11>0,AV11>0,AW11>0,AX11>0,BT11>0,BU11>0,BV11>0,CP11>0,CQ11>0,DL11>0),"Yes","")))))

but no difference, I think I probably messed up the brackets now entirely.

The lookup should always find something based on the attached example. Value being matched is a text.
I found the Ms version for mac that I am using 16.54 (21101001)

[protonLeah]

Please Login or Register  to view this content.

named range-->
ID.Sets ='Group Info'!$A$14:INDEX('Group Info'!$D:$D,MATCH("zzzzz",'Group Info'!$D:$D))

[Gregb11]

I didn't really look into the logic, but I just re-adjusted your formula based on how I think you want it to work. In my thinking, you had some parentheses improperly placed. Maybe this?

=IF(AND(VLOOKUP(A11,'Group Info'!$A$14:$D$1026,4,0)=$CW$8,C11>0,D11>0,H11>0,I11>0,AC11>0,AE11>0,AF11>0,BD11>0,BF11>0,CB11>0,CX11>0),"Yes",IF(AND(VLOOKUP(A11,'Group Info'!$A$14:$D$1026,4,0)=$DD$8,C11>0,D11>0,O11>0,P11>0,AL11>0,AM11>0,AN11>0,AO11>0,BL11>0,BM11>0,BN11>0,CI11>0,CJ11>0,DE11>0),"Yes",IF(AND(VLOOKUP(A11,'Group Info'!$A$14:$D$1026,4,0)=$DK$8,C11>0,D11>0,V11>0,AU11>0,AV11>0,AW11>0,AX11>0,BT11>0,BU11>0,BV11>0,CP11>0,CQ11>0,DL11>0),"Yes","")))

[DJunqueira]

Who knows you were not just looking for a consolidation?

Please Login or Register  to view this content.

[InterMed]

Such a good idea to clean up the code with named ranges, thank you!