Forum Discussion

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

Formulas.xlsx13 KB

FORMULA ERROR

Formulas and Functions

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

3 Replies

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

Haytham Amairah
Silver Contributor
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
- Chandrakanth K
  Copper Contributor
  Aug 14, 2018
  Hello Haytham Amairah ,
  
  Thank you for your support. This helped.
  
  Regards,
  Chandrakanth.K