Forum Discussion

Chandrakanth K's avatar
Chandrakanth K
Copper Contributor
Aug 13, 2018
Solved

Need Support with Index Match and If Function

Hello All,   I have written a formula with a combination of If and Index functions. Below is the logic and I am able to get the required results. But I  need to add one more condition I,e.. this f...
FORMULA ERROR
Formulas and Functions
  • Haytham Amairah's avatar
    Haytham Amairah
    Aug 13, 2018

    Hi,

     

    Let's suppose that the all possible values in column B are as follows:

    • 289C
    • 389C
    • 489C

     

    And if the value is 289C, then add 1 day to the date resulted by the formula, but if the value is 389C, then add 2 days, and if the value is 489C, then add 3 days.

     

    If so, please try to update the formula as follows:

     

    =IF(B2="289C",
    IF(X2="D",(IF(AND(Z2="Z999",Y2="Ready For Payment"),INDEX(Calendar!E:E,MATCH((IF(N2<TODAY(),TODAY()+1,N2+1)), Calendar!E:E,-1)),(IF(AND(AB2="Wkly",Y2="Ready For Payment"),INDEX(Calendar!E:E,MATCH((IF(N2<TODAY(),TODAY()+1,N2+1)), Calendar!E:E,-1)),(IF(AND(U2="MP",Y2="Ready For Payment"),INDEX(Calendar!C:C,MATCH((IF(N2<TODAY(),TODAY()+1,N2+1)),Calendar!C:C,-1)),(IF(AND(U2<>"MP",Y2="Ready For Payment"),INDEX(Calendar!E:E,MATCH((IF(N2<TODAY(),TODAY()+1,N2+1)),Calendar!E:E,-1)),"")))))))),(IF(X2="F",(IF(AND(Z2="Z999",Y2="Ready For Payment"),INDEX(Calendar!F:F,MATCH((IF(N2<TODAY(),TODAY()+1,N2+1)), Calendar!F:F,-1)),(IF(AND(AB2="Wkly",Y2="Ready For Payment"),INDEX(Calendar!F:F,MATCH((IF(N2<TODAY(),TODAY()+1,N2+1)), Calendar!F:F,-1)),(IF(AND(U2="MP",Y2="Ready For Payment"),INDEX(Calendar!D:D,MATCH((IF(N2<TODAY(),TODAY()+1,N2+1)),Calendar!D:D,-1)),(IF(AND(U2<>"MP",Y2="Ready For Payment"),INDEX(Calendar!F:F,MATCH((IF(N2<TODAY(),TODAY()+1,N2+1)),Calendar!F:F,-1)),"")))))))),"")))
    +1,
    IF(B2="389C",
    IF(X2="D",(IF(AND(Z2="Z999",Y2="Ready For Payment"),INDEX(Calendar!E:E,MATCH((IF(N2<TODAY(),TODAY()+1,N2+1)), Calendar!E:E,-1)),(IF(AND(AB2="Wkly",Y2="Ready For Payment"),INDEX(Calendar!E:E,MATCH((IF(N2<TODAY(),TODAY()+1,N2+1)), Calendar!E:E,-1)),(IF(AND(U2="MP",Y2="Ready For Payment"),INDEX(Calendar!C:C,MATCH((IF(N2<TODAY(),TODAY()+1,N2+1)),Calendar!C:C,-1)),(IF(AND(U2<>"MP",Y2="Ready For Payment"),INDEX(Calendar!E:E,MATCH((IF(N2<TODAY(),TODAY()+1,N2+1)),Calendar!E:E,-1)),"")))))))),(IF(X2="F",(IF(AND(Z2="Z999",Y2="Ready For Payment"),INDEX(Calendar!F:F,MATCH((IF(N2<TODAY(),TODAY()+1,N2+1)), Calendar!F:F,-1)),(IF(AND(AB2="Wkly",Y2="Ready For Payment"),INDEX(Calendar!F:F,MATCH((IF(N2<TODAY(),TODAY()+1,N2+1)), Calendar!F:F,-1)),(IF(AND(U2="MP",Y2="Ready For Payment"),INDEX(Calendar!D:D,MATCH((IF(N2<TODAY(),TODAY()+1,N2+1)),Calendar!D:D,-1)),(IF(AND(U2<>"MP",Y2="Ready For Payment"),INDEX(Calendar!F:F,MATCH((IF(N2<TODAY(),TODAY()+1,N2+1)),Calendar!F:F,-1)),"")))))))),"")))
    +2,
    IF(B2="489C",
    IF(X2="D",(IF(AND(Z2="Z999",Y2="Ready For Payment"),INDEX(Calendar!E:E,MATCH((IF(N2<TODAY(),TODAY()+1,N2+1)), Calendar!E:E,-1)),(IF(AND(AB2="Wkly",Y2="Ready For Payment"),INDEX(Calendar!E:E,MATCH((IF(N2<TODAY(),TODAY()+1,N2+1)), Calendar!E:E,-1)),(IF(AND(U2="MP",Y2="Ready For Payment"),INDEX(Calendar!C:C,MATCH((IF(N2<TODAY(),TODAY()+1,N2+1)),Calendar!C:C,-1)),(IF(AND(U2<>"MP",Y2="Ready For Payment"),INDEX(Calendar!E:E,MATCH((IF(N2<TODAY(),TODAY()+1,N2+1)),Calendar!E:E,-1)),"")))))))),(IF(X2="F",(IF(AND(Z2="Z999",Y2="Ready For Payment"),INDEX(Calendar!F:F,MATCH((IF(N2<TODAY(),TODAY()+1,N2+1)), Calendar!F:F,-1)),(IF(AND(AB2="Wkly",Y2="Ready For Payment"),INDEX(Calendar!F:F,MATCH((IF(N2<TODAY(),TODAY()+1,N2+1)), Calendar!F:F,-1)),(IF(AND(U2="MP",Y2="Ready For Payment"),INDEX(Calendar!D:D,MATCH((IF(N2<TODAY(),TODAY()+1,N2+1)),Calendar!D:D,-1)),(IF(AND(U2<>"MP",Y2="Ready For Payment"),INDEX(Calendar!F:F,MATCH((IF(N2<TODAY(),TODAY()+1,N2+1)),Calendar!F:F,-1)),"")))))))),"")))
    +3,"")))

     

     

    Regards

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Aug 16, 2018

As a variant

=IFERROR(IF(Y2="Ready For Payment",
   IF(X2="D",
      IF(Z2="Z999",  INDEX(Calendar!E:E,MATCH(MAX(N2,TODAY())+1, Calendar!E:E,-1)),
      IF(AB2="Wkly", INDEX(Calendar!E:E,MATCH(MAX(N2,TODAY())+1, Calendar!E:E,-1)),
      IF(U2="MP",    INDEX(Calendar!C:C,MATCH(MAX(N2,TODAY())+1,Calendar!C:C,-1)),
                     INDEX(Calendar!E:E,MATCH(MAX(N2,TODAY())+1,Calendar!E:E,-1))))),
   IF(X2="F",
      IF(Z2="Z999",  INDEX(Calendar!F:F,MATCH(MAX(N2,TODAY())+1, Calendar!F:F,-1)),
      IF(AB2="Wkly", INDEX(Calendar!F:F,MATCH(MAX(N2,TODAY())+1, Calendar!F:F,-1)),
      IF(U2="MP",    INDEX(Calendar!D:D,MATCH(MAX(N2,TODAY())+1,Calendar!D:D,-1)),
                     INDEX(Calendar!F:F,MATCH(MAX(N2,TODAY())+1,Calendar!F:F,-1))))),
   "")),
"")+(B2="289C")+(B2="389C")*2+(B2="489C")*3,"")

Resources