Help please - nested IF AND OR statement

[ColletteLuffman]

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 and Cell Q26 is <1.8 than "Pump1" OR

IF Cell M28=4 and Cell Q26 is >1.8 than but < 3.3 "Pump2" OR

IF Cell M28=4 and Cell Q26 is >3.3 than but < 3.7 "Pump3" OR

IF Cell M28=5 and Cell Q26 is <1.3 than "Pump1" OR

IF Cell M28=5 and Cell Q26 is >1.3 than but < 2.8 "Pump2" OR

IF Cell M28=5 and Cell Q26 is >2.8 than but < 3.3 "Pump3" OR

IF Cell M28=6 and Cell Q26 is <0.7 than "Pump1" OR

IF Cell M28=6 and Cell Q26 is >0.7 than but < 2.4 "Pump2" OR

IF Cell M28=6 and Cell Q26 is >2.4 than but < 3.0 "Pump3" Else "NA"

[#2]

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

[#3]

@OliverScheurich 

Hi It only seems to be showing pump 1?

[#4]

@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:

[#5]

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