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

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))
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.
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.
Rgonzo1971

Hi,

pls try
``````=INDEX(A1:A6,IF(C12="Yes",3,0)+MATCH(C11,{0,500.000000000001,3000,150000}))
``````
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

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.