SOLVED

Need Support with Index Match and If Function

Copper Contributor

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 function should work based on company code in column "B" I have 3 different values in Column "B" and each value the calendar would change. I am unable add condition =If(B2="289C" and so on. Need help

=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 Replies
best response confirmed by Chandrakanth K (Copper Contributor)
Solution

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

Hello Haytham Amairah ,

 

Thank you for your support. This helped.

 

Regards,

Chandrakanth.K

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,"")
1 best response

Accepted Solutions
best response confirmed by Chandrakanth K (Copper Contributor)
Solution

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

View solution in original post