How do I get IF(AND) to work with multiple conditions?-CodePudding

I'm trying to make a page calculator that returns a prescribed value depending on what is input into three dropdown cells.

Dropdown cells are:

columns (options are 1 or 2);
trim size (range of options);
text design (range of options).

I can't seem to get the hang of the IF function. This returns what I want it to:

=IF(AND(B7=Sheet1!E2,B8=Sheet1!C2,B9=Sheet1!A2),467,FALSE)

But now I need to add a ton of other combinations of columns, trim sizes and text designs, the choices of which will determine which number is returned. Adding another line with one variation at a time doesn't seem to be working, e.g.

=IF(AND(B7=Sheet1!E2,B8=Sheet1!C2,B9=Sheet1!A2),467,FALSE), IF(AND(B7=Sheet1!E2,B8=**Sheet1!C3**,B9=Sheet1!A2),570,FALSE)

I just get #VALUE!

Any advice?

CodePudding user response：

You just need to nest the If conditions properly with proper syntax - parenthesis, comma need to be accurately placed.

Try adding each If statement in it own line:

=IF( AND(B4=Sheet1!E2,B5=Sheet1!C2,B6=Sheet1!A2),
     Sheet1!J2,
       IF(AND(B4=Sheet1!E2,B5=Sheet1!C3,B6=Sheet1!A2),
          Sheet1!J3, 
          IF(AND(B4=Sheet1!E2,Calculator!B5=Sheet1!C4,Calculator!B6=Sheet1!A2),
             Sheet1!E3,
             False
            )
          )
       )
    )

You need to close each bracket properly too

Above process should help you understand the issue in your formula, But the better way to solve it is using the Switch Formula. It makes nesting conditions easier:

=SWITCH(TRUE,
AND(B4=Sheet1!E2,B5=Sheet1!C2,B6=Sheet1!A2), Sheet1!J2,
AND(B4=Sheet1!E2,B5=Sheet1!C3,B6=Sheet1!A2), Sheet1!J3,
AND(B4=Sheet1!E2,Calculator!B5=Sheet1!C4,Calculator!B6=Sheet1!A2), Sheet1!E3,
"If nothing matches - Default"
)

the first line in the formula (=SWITCH(TRUE) means that, stop searching when you find a condition that is TRUE