I have 3 drop down boxes that I want one cell to choose an answer for based on the selections for each drop down. I can use the IF AND function to produce the one scenario of the result I want but I can't get it to do it for more than one. Here is that formula:

=if((AND(A13="Picasso 1000",C13="RIC",E13="Batteries")),"$4,392","")

I either can't figure out how to properly nest several of these types of formulas for other outcomes or I am not using the right kind of formula to produce the result I need.

  • Welcome. So what is the question? Can you show us what the issue is? Please read how to and share a test sheet so as you can be easier helped. Jun 12, 2020 at 17:51

2 Answers 2


You don't need any IF()s.

You can setup the formula as a set of mutually exclusive Boolean factors times payoffs like:

=factor1*134.54 + factor2*56.98 + factor3*100.34 + ...

just insure that only one of the factors is 1 and all the others are 0. For example:

=AND(a1="green",a2="green",a3="green")*134.54 + AND(a1="red",a2="red",a3="red")*56.98 + AND(a1=5,a2=6,a3=99)*100.34

only one of the three terms can contribute.


You can also nest another if function inside your first function, replacing the "" you have at the end of the function.

=if(AND(A13="Picasso 1000",C13="RIC",E13="Batteries"),"$4,392",if(AND(A13="value",C13="value2",E13="value3"),"outputvalue",""))

This can be repeated indefinitely so long as you have the right number of parentheses at the end.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.