Forum Discussion
Help please - nested IF AND OR statement
Hi I am struggling with a nested IF AND OR statement, if i write it down in English please can somebody help me with the code please IF Cell M28=3 and Cell Q26 is <2.2 than "Pump1" IF Cell M28=4...
the formula of OliverScheurich works!
I like to use IFS() with so many nested IF().
=IFS(
AND(M28=3,Q26<2.2),"Pump1",
AND(M28=4,Q26<1.8),"Pump1",
AND(M28=4,Q26>1.8,Q26<3.3), "Pump2",
AND(M28=4,Q26>3.3,Q26<3.7), "Pump3",
AND(M28=5,Q26<1.3),"Pump1",
AND(M28=5,Q26>1.3,Q26<2.8), "Pump2",
AND(M28=5,Q26>2.8,Q26<3.3), "Pump3",
AND(M28=6,Q26<0.7),"Pump1",
AND(M28=6,Q26>0.7,Q26<2.4), "Pump2",
AND(M28=6,Q26>2.4,Q26<3), "Pump3",
TRUE,NA())
I would like to draw your attention to the fact that you always use e.g. > 0.7 and < 0.7. You have not caught the case =0.7. Using either a <= or a >= somewhere would resolve this if necessary.
With this formula, that would be done. With option 1 in XLOOKUP one achieves: exact match - If none found, return the next larger item.
=SWITCH(M28,
3,IF(Q26<2.2,"Pump1",NA()),
4,XLOOKUP(Q26,{1.8;3.3;3.7},{"Pump1";"Pump2";"Pump3"},NA(),1),
5,XLOOKUP(Q26,{1.3;2.8;3.3},{"Pump1";"Pump2";"Pump3"},NA(),1),
6,XLOOKUP(Q26,{0.7;2.4;3},{"Pump1";"Pump2";"Pump3"},NA(),1),
NA())