Support with If Function

[Chandrakanth K]

Hello All,

I have written below if function to derive the payment type for vendors. I am stuck with one condition. If my column "I" is blank and if my column "C" is not equal to "C" or "K" then the result should be "MDI" I was able to add condition for "C" but unable to add condition for "K"

=(IF(OR(RIGHT(TRIM(C3),1)="9",RIGHT(TRIM(C3),1)="X"),"No Pay Run",(IF(AND(TRIM(I3)="",LEN(TRIM(C3))>1,(TRIM(C3))<>"C"),"MDI",(IF(AND(TRIM(I3)="",(TRIM(C3))<>"C"),"MDI",(IF(OR(TRIM(C3)="GM"),"IC Pay Run",(IF(OR(RIGHT(TRIM(C3),1)="T",RIGHT(TRIM(C3),1)="U"),"F",(IF(OR(RIGHT(TRIM(C3),1)="C",RIGHT(TRIM(C3),1)="D",RIGHT(TRIM(C3),1)="P",RIGHT(TRIM(C3),1)="Q",RIGHT(TRIM(C3),1)="E",RIGHT(TRIM(C3),1)="B",RIGHT(TRIM(C3),1)="X",RIGHT(TRIM(C3),1)="S",RIGHT(TRIM(C3),1)="K"),"D","MDI"))))))))))))

Attached sample excel report. Please help

Regards,

Chandrakanth.K

[#2]

Hi,

Perhaps something like this

=IF(OR(RIGHT(TRIM(C2),1)="9",
      RIGHT(TRIM(C2),1)="X"),"No Pay Run",
   IF(AND(TRIM(I2)="",
      LEN(TRIM(C2))=1,
      OR(TRIM(C2)<>"C",
         TRIM(C2)<>"K")),"MDI",
   IF(OR(TRIM(C2)="GM"),"IC Pay Run",
   IF(OR(RIGHT(TRIM(C2),1)="T",
      RIGHT(TRIM(C2),1)="U"),"F",
   IF(OR(RIGHT(TRIM(C2),1)="C",
      RIGHT(TRIM(C2),1)="D",
      RIGHT(TRIM(C2),1)="P",
      RIGHT(TRIM(C2),1)="Q",
      RIGHT(TRIM(C2),1)="E",
      RIGHT(TRIM(C2),1)="B",
      RIGHT(TRIM(C2),1)="X",
      RIGHT(TRIM(C2),1)="S",
      RIGHT(TRIM(C2),1)="K"),"D","MDI")
))))

if not simplify the rest

[#3]

Thank you Sergei Baklan
Regards,
Chandrakanth.K