Is there a way of creating an IF AND AND function in Excel? Is there anyway to create an if function that will work out if one cell contains "X" and another cell contains "Y" then the result will be another set cell ="Z"?

So say if Cell B1 contains "X" and Cell C20 contains "Y" then return the following value from D20? So effectively looking for an IF, AND, AND function ... is this possible? As the follow on from above is:

If Cell B1 contains "X" and Cell C20 contains "Y" then return the following value from D20, if not then check if B1 still contains "X" but Cell C21 contains "Y" instead then the figure needing to be presented would be in D21

Both of the formulas below should return what you asked for, although you didn't specify what you want returned if B1 does notcontain X or if B1 contains X but neither C20 or C21 contain Y.As written, they will both will return a blank cell in those cases:

=IF(AND(B1="X",C20="Y"),D20,IF(AND(B1="X",C21="Y"),D21,""))

=IF(B1="X",IF(C20="Y",D20,IF(C21="Y",D21,"")),"")

The first formula uses the IF, AND, AND structure that you asked for, while the second simply uses the Nested IF format, resulting in a shorter formula.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll