Avatar of Adrian McNair
Adrian McNair
 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"),A4,A1,       IF(AND(C11>500,C11<3000,C12="yes"),A5,A2,      IF(AND(C11>=3000,C12="yes"),A6,A3)))

PS: I have put some spacing between IF statements just to show some separation.
Microsoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Rob Henson

8/22/2022 - Mon
Shums Faruk

Hi,

You can try below:
=CONCATENATE(IF(AND(C11<=500,C12="yes"),A4,A1),IF(AND(C11>500,C11<3000,C12="yes"),A5,A2),IF(AND(C11>=3000,C12="yes"),A6,A3))
ASKER CERTIFIED SOLUTION
Rgonzo1971

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.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Rob Henson

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.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Rgonzo1971

Hi,

pls try
=INDEX(A1:A6,IF(C12="Yes",3,0)+MATCH(C11,{0,500.000000000001,3000,150000}))

Open in new window

Rob's suggestion has 500 as A5 not what you specified

Regards
Rob Henson

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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Adrian McNair

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.
Adrian McNair

ASKER
Thank You
Rob Henson

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,A1,IF(AND(C11>500,C11<3000),A2,A3)),IF(C11<=500,A4,IF(AND(C11>500,C11<3000),A5,A6)))

or could also use CHOOSE function:

=CHOOSE((C12="Yes")+1,IF(C11<=500,A1,IF(AND(C11>500,C11<3000),A2,A3)),IF(C11<=500,A4,IF(AND(C11>500,C11<3000),A5,A6)))
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.