It seemed simpler to put the conditions in a range (named TestR) and then use:
I have opted for a shorter formula, mainly by hiving off the four ‘list’ items into a named range. Rather than mix AND and OR I opted for just OR by testing for all the possibilities that should give rise to a “No” result. These are that the ColumnC value is not in the list (by trying to match it to the list and failing) as one possibility and that the ColumnA value is not “X” as the other possibility. Otherwise “Yes” should be returned.
The formula you tried has at its core a long OR function. The syntax for this is valid, the problem is with the logic. OR will return
TRUE if any of its tests return true, provided none of them returns an error. You have four tests, so that part of your formula might be represented as
=or(does not contain 1, does not contain 2, does not contain 3, does not contain 4).
Whichever of 1, 2, 3 and 4 (or indeed nothing or 0 or k etc) is contained, three at least of the four tests will PASS (if say it contains 4 then indeed it will not contain 3, etc). When the OR part is included in the rest of your formula it really contributes nothing, just the possibility of an error. Ignoring the error possibility, the overall result then is effectively the same as:
Adjusting to A2 from A3 (so the formula may be entered in B2 where seems first required, then copied down) it may be seen that the OP’s formula merely returns
Yes if there is an
X in the cell immediately to its left, and in all other cases
What seems to be required is “contains” for the tests, rather than “does not contain”, so some small adjustments should be sufficient:
=If(and(A2 = "X",OR(C2="Ready for QA Testing on Stage",C2="Deployed to Production",C2="Ready for Deployment",C2="Ready for QA Testing on Trunk")),"Yes","No")