Forum Discussion
Multiple IF/OR Formula Help
There are 4 possible jobs, the following cells will contain either a "Yes or a "No" that they were completed: Job cells are: job 1 = X105, job 2 = AW105, job 3 = X133, job 4 = AW133 The first o...
IF you have 365 then you can use FILTER to make it easy but do you want a 3rd in AK180 and 4th in AK181? or only a max of 2? Here is for a max of 2:
=TAKE(FILTER( VSTACK(Q108, AP108, Q136, AP136), VSTACK(X105, AW105,X133,AW133)="yes", ""),2)
If you don't have Excel365 you can try this:
=IFERROR(CHOOSE(MIN( 1+SUM(--(MMULT( --MID( CONCAT( --(X105="yes"),--(AW105="yes"),--(X133="yes"),--(AW133="yes")),{1,2,3,4},1), {1,1,1; 1,1,1; 0,1,1; 0,0,1} )<2))), Q108, AP108, Q136, AP136),"")
Thanks so much for your help! I have 365 but when I use the 1st formula, I get a Spill error in both cells. I put the second formula in both AK178 and AK179, but I still have the same problem as before. If Run 1 is a "Yes" and Run 2 is a "Yes" as well, both AK178 & 179 populate with TD 1. I need for AK179 to populate with TD 2 if TD 1 was already used.
- If you have 365 the first formula should ONLY be in the first cell and it will output into BOTH cells if needed (i.e. it will 'spill')
as for the second equation that was ONLY for the 2nd value as you said you could already get the 1st- The formulas are going in AK178 & 179. If only 1 job was completed, my formula in AK178 would work. If more than 1 job was completed I don't know how to make the formula in AK179 so it would input the next completed job information. This is what I had in AK178 already:
=IF(X105="Yes",Q108,IF(X105="No",IF(AW105="Yes",AP108,IF(X105="No",IF(AW105="No",IF(X133="Yes",Q136,IF(X105="No",IF(AW105="No",IF(X133="No",IF(AW133="Yes",AP136))))))))))
When I use the above formula in both 178 & 179, then if there is more than 1 completed job, like in the examples below, both AK178 & 179 would fill with the same completed job TD instead of AK179 moving on to the next job that was completed. I don't know how to make the formula move to the next completed job and not keep entering the same TD info in both AK178 & 179.
If Job 1 is a "No", Job 2 is a "Yes", Job 3 is a "Yes", and Job 4 is a "No".......
then AK178 should equal AP108
and AK179 should equal Q136
or.............
If Job 1 is a "Yes", Job 2 is a "No", Job 3 is a "Yes", and Job 4 is a "No".......
then AK178 should equal Q108
and AK179 should equal Q136
I don't think i'm explaining this very well...sorry.- Ok so you have 365 so put the first formula I did above somewhere with space below it (for example if cells AK180:AK184 are all blank then put it in AK180) and see if it spits out what you need.
As for your above formula you do NOT need to repeat the "No" unless you could have something other than Yes/No and even then if NOT yes then do you care if it is No or n/a or blank or ...? So:
=IF(X105="Yes",Q108,IF(AW105="Yes",AP108,IF(X133="Yes",Q136,IF(AW133="Yes",AP136))))
alternatively you could use IFS:
=IFS(X105="Yes",Q108,AW105="Yes",AP108,X133="Yes",Q136,AW133="Yes",AP136)
as for the second cell you can try the formula I did above or might be easier to try the more direct approach:
=IF(AND(X105="Yes",AW105="Yes"),AP108,IF(AND(OR(X105="Yes",AW105="Yes"),X133="Yes"),Q136,IF(AND(OR(X105="Yes",AW105="Yes",X133="Yes"),AW133="Yes"),AP136)))
which again using IFS could be:
=IF(AND(X105="Yes",AW105="Yes"),AP108,AND(OR(X105="Yes",AW105="Yes"),X133="Yes"),Q136,AND(OR(X105="Yes",AW105="Yes",X133="Yes"),AW133="Yes"),AP136)
but that FILTER option is powerful and something good to learn.
