Shorten Nested if AND Formula

[mohdamir1989]

Hello all,

I have a formula as below working very well I just want it shortened and clean if by any way possible. It is self explanatory still I can explain if required

Basically If checking from M column if right 3 words equals Bel and AB is equals or less than 72 hours it will be writing "L1" so changing hours will result different levels.

=IF(AND(RIGHT(M1223,3)="Bel",AB1223<=72),"L1",IF(AND(RIGHT(M1223,3)="Bel",AB1223<=96),"L2",IF(AND(RIGHT(M1223,3)="Bel",AB1223<=144),"L3",IF(AND(RIGHT(M1223,3)="ase",AB1223<=48),"L1",IF(AND(RIGHT(M1223,3)="ase",AB1223<=72),"L2",IF(AND(RIGHT(M1223,3)="ase",AB1223<=120),"L3",""))))))

Your experience sharing is highly appreciated

Best Regards

 

[FormR]

Hi, if you wanted to stick with nested IF()'s here is one alternative:

=IF(RIGHT(M1223,3)="bel",IF(AB1223<=72,"L1",IF(AB1223<=96,"L2",IF(AB1223<=144,"L3",""))),IF(RIGHT(M1223,3)="Ase",IF(AB1223<=48,"L1",IF(AB1223<=72,"L2",IF(AB1223<=120,"L3",""))),""))

Or for a different approach entirely..:

=IFNA("L"&4-MATCH(AB1223,CHOOSE(MATCH(RIGHT(M1223,3),{"Bel","Ase"},0),{144,96,72},{120,72,48}),-1),"")

 

[than_gold]

Hello,
Not sure which excel version you are using.
Try with countifs which works on below versions.
[FONT=&quot] [/FONT][FONT=&quot][/FONT][FONT=&quot]Excel 2016[/FONT][FONT=&quot] [/FONT][FONT=&quot]Excel 2013[/FONT][FONT=&quot] [/FONT][FONT=&quot]Excel 2010[/FONT][FONT=&quot] [/FONT][FONT=&quot]Excel 2007[/FONT]

 

[snjpverma]

Try this

=IF(RIGHT(A1,3)="Bel",LOOKUP(A2,{0,"L1";73,"L2";97,"L3";145,""}),IF(RIGHT(A1,3)="ase",LOOKUP(A2,{0,"L1";49,"L2";73,"L3";121,""}),""))

 

[mohdamir1989]

Hi, if you wanted to stick with nested IF()'s here is one alternative:

=IF(RIGHT(M1223,3)="bel",IF(AB1223<=72,"L1",IF(AB1223<=96,"L2",IF(AB1223<=144,"L3",""))),IF(RIGHT(M1223,3)="Ase",IF(AB1223<=48,"L1",IF(AB1223<=72,"L2",IF(AB1223<=120,"L3",""))),""))

Or for a different approach entirely..:

=IFNA("L"&4-MATCH(AB1223,CHOOSE(MATCH(RIGHT(M1223,3),{"Bel","Ase"},0),{144,96,72},{120,72,48}),-1),"")

Thank you FormR for kind help and sharing experience. Second formula is returning #NAME ? error. Maybe I'm missing something.

Best Regards

 

[FormR]

Second formula is returning #NAME ? error. Maybe I'm missing something.

Hi, IFNA() is only available in XL2013+ you can try using IFERROR() instead:

=IFERROR("L"&4-MATCH(AB1223,CHOOSE(MATCH(RIGHT(M1223,3),{"Bel","Ase"},0),{144,96,72},{120,72,48}),-1),"")