Nesting IF AND funtions in Google Sheets

Question

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.

Answer 1

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.

Answer 2

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.