Multiple IF/OR Formula Help

Question

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 one of these job cells, in numerical order, to have a "yes", needs to have the corresponding TD cell populate in cell AK178.

TD Cells are:

TD 1 = Q108, TD 2 = AP108, TD 3 = Q136, TD 4 = AP136

My problem is that there can be more than 1 job completed. Cell AK178 is for the 1st job that has a "yes".

Cell AK179 needs to be populated with the next job that has a "yes" in numerical order.

So, 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 can write the formula for one but not if there are 2 completed jobs. Help please!!

m_tarler · Answer

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),"")

erin-5304 · Answer

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 &amp; 179 populate with TD 1. I need for AK179 to populate with TD 2 if TD 1 was already used.

m_tarler · Answer

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

erin-5304 · Answer

The formulas are going in AK178 &amp; 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 &amp; 179, then if there is more than 1 completed job, like in the examples below, both AK178 &amp; 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 &amp; 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 AP108and AK179 should equal Q136or.............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 Q108and AK179 should equal Q136I don't think i'm explaining this very well...sorry.

m_tarler · Answer

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.

Forum Discussion

Multiple IF/OR Formula Help

Share

Resources