Aug 13 2018 06:21 AM
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)),"")))))))),"")))
Aug 13 2018 07:40 AM - edited Aug 13 2018 07:42 AM
SolutionHi,
Let's suppose that the all possible values in column B are as follows:
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
Aug 14 2018 01:51 AM
Aug 16 2018 04:40 PM
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,"")
Aug 13 2018 07:40 AM - edited Aug 13 2018 07:42 AM
SolutionHi,
Let's suppose that the all possible values in column B are as follows:
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