Help please - nested IF AND OR statement

ColletteLuffman

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())

ColletteLuffman 

=IF(AND(M28=3,Q26<2.2),"Pump1",
IF(AND(M28=4,Q26<1.8),"Pump1",
IF(AND(M28=4,Q26>1.8,Q26<3.3),"Pump2",
IF(AND(M28=4,Q26>3.3,Q26<3.7),"Pump3",
IF(AND(M28=5,Q26<1.3),"Pump1",
IF(AND(M28=5,Q26>1.3,Q26<2.8),"Pump2",
IF(AND(M28=5,Q26>2.8,Q26<3.3),"Pump3",
IF(AND(M28=6,Q26<0.7),"Pump1",
IF(AND(M28=6,Q26>0.7,Q26<2.4),"Pump2",
IF(AND(M28=6,Q26>2.4,Q26<3),"Pump3","NA"))))))))))

If one of the highlighted conditions is true the formula returns "Pump1". Otherwise it returns "Pump2", "Pump3" or "NA". For example: