I was wondering if anyone could help with the following. I have found one question and answer relating to my query (I think!!) but I can't break it down to do what I want.
I have created a spreadsheet and used the IF & AND functions to do half of what I want.
Basically if the answer is "no" in one column and one of four options in another (111,112,118,119) I want it to bring in the value of C2. This is what I have come up with:
=IF(AND(E2= "No",F2=112),C2,0)
This part works fine but I also need it to bring in the value of C2 if "Yes" (E2) is selected AND the value of D2 is equal to F2 - this is what I have come up with
=IF(AND(E2="No",F2=111),C2,0),IF(E2 ="Yes",D2=F2,C2)
It doesn't work.
I found the answer linked at the top of the post, but my Excel skills aren't up to pulling it apart to fit what I want.
The reason you can't get the formula to work is because there are two separate formula thrown together in a cell:
=IF(AND(E2="No",F2=111),C2,0) - alone this is a complete formula and will return either C2 or 0.
If you want to nest your if statements you need to look at chaining them off each other: If([something],[do this],If([something else],[do this],If(... you get the point.
In terms of what you are trying to achieve there is a more effective method though, by using another formula, MATCH() that returns the position of a match in the list (array or range) you provide. You can just check whether the result of that formula is a number, like so:
=IF(AND(E2="No",ISNUMBER(MATCH(F2,{111,112,118,119}))),C2,0)
EDIT
So instead of providing 0 when the result of the AND() is false, we kick off another if that is your "Yes" clause... This then gives the 0 if it fails... Flip them round if you want "Yes" to be calculated first.
=IF(AND(E2="No",OR(F2=111,F2=112,F2=118,F2=119)),C2,IF(E2="Yes",D2=F2,0))
So the final result based off your comment would have been:
=IF(OR(AND(E2="Yes",D2=F2),AND(E2="No",OR(F2=111,F2=112,F2=118,F2=119))),C2,0)
