asked on
Multiple (nested?) IF statements with AND / please help - Too many arguments
I am trying to get this working, but getting too many arguments. I have tried searching on the forum and there are many entries, but have not found any with the use of multiple AND's
C11 is a number entry by the user. C12 is drop down with yes and no.
Based on the value of C11 that is entered and C12 (yes/No) selection , I want to display A1 through A6.
C11 value ranges that I need are : <=500, between 500 & 2999 and >= 3000
This is the formula that I put in &
=IF(AND(C11<=500,C12="yes"
PS: I have put some spacing between IF statements just to show some separation.
C11 is a number entry by the user. C12 is drop down with yes and no.
Based on the value of C11 that is entered and C12 (yes/No) selection , I want to display A1 through A6.
C11 value ranges that I need are : <=500, between 500 & 2999 and >= 3000
This is the formula that I put in &
=IF(AND(C11<=500,C12="yes"
PS: I have put some spacing between IF statements just to show some separation.
Microsoft ExcelMicrosoft Office
Last Comment
Rob Henson
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Hi,
pls try
Regards
pls try
=INDEX(A1:A6,IF(C12="Yes",3,0)+MATCH(C11,{0,500.000000000001,3000,150000}))
Regards
I had A1 to A3 for the "No" results and A4 to A6 for the "Yes" results.
I included the 0 in the array to avoid an error but found that I did not have to reduce the Match by one row as a consequence. Seems it still works without the CSE entry.
See attached file with all suggestions.
Thanks
Rob
Yes-no-values.xlsx
I included the 0 in the array to avoid an error but found that I did not have to reduce the Match by one row as a consequence. Seems it still works without the CSE entry.
See attached file with all suggestions.
Thanks
Rob
Yes-no-values.xlsx
ASKER
Rgonzo1971/Rob, thanks for helping out. Both of your formulas worked out for me. (Rob, I decided to go with Rgonzo's 1st formula mostly because I want to keep it as simple as possible for my colleagues who might want to tweak it later on. I found out that most people are able to comprehend IT statements more easily than INDEX/MATCH). And I was not able to understand the array entry 15000 etc.
Nevertheless both works and I appreciate your inputs. Keep up the good work.
Nevertheless both works and I appreciate your inputs. Keep up the good work.
ASKER
Thank You
Same principle as accepted solution but different syntax may be easier to understand/update as cells with results are sequential within the formula:
=IF(C12="No",IF(C11<=500,A
or could also use CHOOSE function:
=CHOOSE((C12="Yes")+1,IF(C
=IF(C12="No",IF(C11<=500,A
or could also use CHOOSE function:
=CHOOSE((C12="Yes")+1,IF(C
You can try below:
=CONCATENATE(IF(AND(C11<=5