Need for more than 64 levels of nested - Simplify formula

[Nikoss]

Hello guys,

I am not that experienced with Excel so I need your help with this. I have a very long formula with 64 levels of nested and I need to add more which is not allowed. I will copy and paste the formula here but although it might look very complicated, it is pretty much the same thing over and over again but with different values. I need to find a way to simplify it, so I don't get the error of 64 levels of nested if I add more. I thought to maybe use VLOOKUP but I have not used it before so I am not sure if it would work. Here is the formula:

=IF(AND(G4=2016,I4="B250 4M",EW2<'Pricing Matrix'!$D$3),"Below",IF(AND(G4=2016,I4="B250 4M",EW2>='Pricing Matrix'!$D$3,EW2<='Pricing Matrix'!$E$3),"",IF(AND(G4=2016,I4="B250 4M",EW2>'Pricing Matrix'!$E$3),"Over",IF(AND(G4=2017,I4="B250 4M",EW2<'Pricing Matrix'!$D$9),"Below",IF(AND(G4=2017,I4="B250 4M",EW2>='Pricing Matrix'!$D$9,EW2<='Pricing Matrix'!$E$9),"",IF(AND(G4=2017,I4="B250 4M",EW2>'Pricing Matrix'!$E$9),"Over",IF(AND(G4=2018,I4="B250 4M",EW2<'Pricing Matrix'!$D$29),"Below",IF(AND(G4=2018,I4="B250 4M",EW2>='Pricing Matrix'!$D$29,EW2<='Pricing Matrix'!$E$29),"",IF(AND(G4=2018,I4="B250 4M",EW2>'Pricing Matrix'!$E$29),"Over",IF(AND(G2=2016,I2="C300 4M",EW2<'Pricing Matrix'!$D$4),"Below",IF(AND(G2=2016,I2="C300 4M",EW2>='Pricing Matrix'!$D$4,EW2<='Pricing Matrix'!$E$4),"",IF(AND(G2=2016,I2="C300 4M",EW2>'Pricing Matrix'!$E$4),"Over",IF(AND(G2=2017,I2="C300 4M",EW2<'Pricing Matrix'!$D$10),"Below",IF(AND(G2=2017,I2="C300 4M",EW2>='Pricing Matrix'!$D$10,EW2<='Pricing Matrix'!$E$10),"",IF(AND(G2=2017,I2="C300 4M",EW2>'Pricing Matrix'!$E$10),"Over",IF(AND(G2=2018,I2="C300 4M",EW2<'Pricing Matrix'!$D$30),"Below",IF(AND(G2=2018,I2="C300 4M",EW2>='Pricing Matrix'!$D$30,EW2<='Pricing Matrix'!$E$30),"",IF(AND(G2=2018,I2="C300 4M",EW2>'Pricing Matrix'!$E$30),"Over",IF(AND(G4=2017,I4="C",EW2<'Pricing Matrix'!$D$12),"Below",IF(AND(G4=2017,I4="C300C 4M",EW2>='Pricing Matrix'!$D$12,EW2<='Pricing Matrix'!$E$12),"",IF(AND(G4=2017,I4="C300C 4M",EW2>'Pricing Matrix'!$E$12),"Over",IF(AND(G4=2018,I4="C300C 4M",EW2<'Pricing Matrix'!$D$31),"Below",IF(AND(G4=2018,I4="C300C 4M",EW2>='Pricing Matrix'!$D$31,EW2<='Pricing Matrix'!$E$31),"",IF(AND(G4=2018,I4="C300C 4M",EW2>'Pricing Matrix'!$E$31),"Over",IF(AND(G4=2017,I4="C300A 4M",EW2<'Pricing Matrix'!$D$11),"Below",IF(AND(G4=2017,I4="C300A 4M",EW2>='Pricing Matrix'!$D$11,EW2<='Pricing Matrix'!$E$11),"",IF(AND(G4=2017,I4="C300A 4M",EW2>'Pricing Matrix'!$E$11),"Over",IF(AND(G4=2017,I4="C43 4M",EW2<'Pricing Matrix'!$D$13),"Below",IF(AND(G4=2017,I4="C43 4M",EW2>='Pricing Matrix'!$D$13,EW2<='Pricing Matrix'!$E$13),"",IF(AND(G4=2017,I4="C43 4M",EW2>'Pricing Matrix'!$E$13),"Over",IF(AND(G4=2018,I4="C43 4M",EW2<'Pricing Matrix'!$D$32),"Below",IF(AND(G4=2018,I4="C43 4M",EW2>='Pricing Matrix'!$D$32,EW2<='Pricing Matrix'!$E$32),"",IF(AND(G4=2018,I4="C43 4M",EW2>'Pricing Matrix'!$E$32),"Over",IF(AND(G4=2017,I4="C43A 4M",EW2<'Pricing Matrix'!$D$14),"Below",IF(AND(G4=2017,I4="C43A 4M",EW2>='Pricing Matrix'!$D$14,EW2<='Pricing Matrix'!$E$14),"",IF(AND(G4=2017,I4="C43A 4M",EW2>'Pricing Matrix'!$E$14),"Over",IF(AND(G4=2017,I4="C43C 4M",EW2<'Pricing Matrix'!$D$15),"Below",IF(AND(G4=2017,I4="C43C 4M",EW2>='Pricing Matrix'!$D$15,EW2<='Pricing Matrix'!$E$15),"",IF(AND(G4=2017,I4="C43C 4M",EW2>'Pricing Matrix'!$E$15),"Over",IF(AND(G4=2018,I4="C43C 4M",EW2<'Pricing Matrix'!$D$33),"Below",IF(AND(G4=2018,I4="C43C 4M",EW2>='Pricing Matrix'!$D$33,EW2<='Pricing Matrix'!$E$33),"",IF(AND(G4=2018,I4="C43C 4M",EW2>'Pricing Matrix'!$E$33),"Over",IF(AND(G4=2016,I4="CLA250M",EW2<'Pricing Matrix'!$D$6),"Below",IF(AND(G4=2016,I4="CLA250M",EW2>='Pricing Matrix'!$D$6,EW2<='Pricing Matrix'!$E$6),"",IF(AND(G4=2016,I4="CLA250M",EW2>'Pricing Matrix'!$E$6),"Over",IF(AND(G4=2017,I4="CLA250M",EW2<'Pricing Matrix'!$D$17),"Below",IF(AND(G4=2017,I4="CLA250M",EW2>='Pricing Matrix'!$D$17,EW2<='Pricing Matrix'!$E$17),"",IF(AND(G4=2017,I4="CLA250M",EW2>'Pricing Matrix'!$E$17),"Over",IF(AND(G4=2018,I4="CLA250M",EW2<'Pricing Matrix'!$D$34),"Below",IF(AND(G4=2018,I4="CLA250M",EW2>='Pricing Matrix'!$D$34,EW2<='Pricing Matrix'!$E$34),"",IF(AND(G4=2018,I4="CLA250M",EW2>'Pricing Matrix'!$E$34),"Over",IF(AND(G4=2017,I4="CLA45 4M",EW2<'Pricing Matrix'!$D$18),"Below",IF(AND(G4=2017,I4="CLA45 4M",EW2>='Pricing Matrix'!$D$18,EW2<='Pricing Matrix'!$E$18),"",IF(AND(G4=2017,I4="CLA45 4M",EW2>'Pricing Matrix'!$E$18),"Over",IF(AND(G4=2016,I4="GLA250M",EW2<'Pricing Matrix'!$D$7),"Below",IF(AND(G4=2016,I4="GLA250M",EW2>='Pricing Matrix'!$D$7,EW2<='Pricing Matrix'!$E$7),"",IF(AND(G4=2016,I4="GLA250M",EW2>'Pricing Matrix'!$E$7),"Over",IF(AND(G4=2017,I4="GLA250M",EW2<'Pricing Matrix'!$D$22),"Below",IF(AND(G4=2017,I4="GLA250M",EW2>='Pricing Matrix'!$D$22,EW2<='Pricing Matrix'!$E$22),"",IF(AND(G4=2017,I4="GLA250M",EW2>'Pricing Matrix'!$E$22),"Over",IF(AND(G4=2018,I4="GLA250M",EW2<'Pricing Matrix'!$D$36),"Below Pricing Matrix",IF(AND(G4=2018,I4="GLA250M",EW2>='Pricing Matrix'!$D$36,EW2<='Pricing Matrix'!$E$36),"In Accordance With Pricing Matrix",IF(AND(G4=2018,I4="GLA250M",EW2>'Pricing Matrix'!$E$36),"Exceeds Pricing Matrix",IF(AND(G4=2018,I4="GLC300 4M",EW2<'Pricing Matrix'!$D$37),"Below Pricing Matrix",IF(AND(G4=2018,I4="GLC300 4M",EW2>='Pricing Matrix'!$D$37,EW2<='Pricing Matrix'!$E$37),"In Accordance With Pricing Matrix",IF(AND(G4=2018,I4="GLC300 4M",EW2>'Pricing Matrix'!$E$37),"Exceeds Pricing Matrix","N/A")))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

I appreciate your help in advance!

Thank you,

[#2]

I'm glad we finally got something working for you

[#3]

Hello, @mtarler or who can reply...I have similar issue described above but I cannot solve...basically I wrote a beast function with more than 64 levels by using IF and AND but of course it does not work :( ...having a look at the attached, in a nutshell what I am trying to do is: IF A14=B1 AND A15=A2, give me B2...up to IF A14=M1 a AND A15=A11 give me M11 and so on for all the combinations on the rows and columns...is there a way to do that? Thanks

[#4]

@Klod1978 As with most things with Excel there are many solutions including using pivot tables and filters and such but in terms of a simple formula that is even compatible with most versions of excel:

=INDEX($A$1:$M$11,MATCH($A$15,$A$1:$A$11,0),MATCH($A$14,$A$1:$M$1,0))

This formula takes the whole table and uses INDEX to find the value based on finding the row and column using the MATCH function.

[#5]

@Klod1978 

That could be

=INDEX(B2:M11, MATCH(A15,$A$2:$A$11,0), MATCH(A14,$B$1:$M$1,0))

[#6]

@Klod1978 

Another option

=SUMPRODUCT( (B1:M1=A14) * (A2:A11=A15) * B2:M11)

[#7]

@mtarler Thank you very much, now it works fine!!! :)

[#8]

I want need for mor than 64 level of nested - simplify formula

[#9]

@Balamurugan790 

You can do this with a lookup table and VLOOKUP.

See the attached version. You can extend the lookup table on Sheet2. The formulas will automatically use the extra values.

[#10]

@Hans Vogelaar 

thankyou sir.

[#11]

Any chance someone could help me with my extremely long formula? I'm having the same problem.

=IF(G10="None",Datto!G4,IF(G10="2TB S5X ICR",Datto!G5,IF(G10="2TB S5 ICR",Datto!G6,IF(G10="3TB S5 ICR",Datto!G7,IF(G10="4TB S5 ICR",Datto!G8,IF(G10="6TB S5 ICR",Datto!G9,IF(G10="8TB S5 ICR",Datto!G10,IF(G10="12TB S5 ICR",Datto!G11,IF(G10="18TB S5 ICR",Datto!G12,IF(G10="24TB S5 ICR",Datto!G13,IF(G10="36TB S5 ICR",Datto!G14,IF(G10="48TB S5 ICR",Datto!G15,IF(G10="60TB S5 ICR",Datto!G16,IF(G10="1 TB Virt ICR",Datto!G17,IF(G10="2 TB Virt ICR",Datto!G18,IF(G10="3 TB Virt ICR",Datto!G19,IF(G10="4 TB Virt ICR",Datto!G20,IF(G10="6 TB Virt ICR",Datto!G21,IF(G10="8 TB Virt ICR",Datto!G22,IF(G10="12 TB Virt ICR",Datto!G23,IF(G10="18 TB Virt ICR",Datto!G24,IF(G10="24 TB Virt ICR",Datto!G25,IF(G10="36 TB Virt ICR",Datto!G26,IF(G10="48 TB Virt ICR",Datto!G27,IF(G10="60 TB Virt ICR",Datto!G28,IF(G10="80 TB Virt ICR",Datto!G29,IF(G10="100 TB Virt ICR",Datto!G30,IF(G10="2TB S5X TBR",Datto!I31,IF(G10="2TB S5 TBR",Datto!I32,IF(G10="3TB S5 TBR",Datto!I33,IF(G10="4TB S5 TBR",Datto!I34,IF(G10="6TB S5 TBR",Datto!I35,IF(G10="8TB S5 TBR",Datto!I36,IF(G10="12TB S5 TBR",Datto!I37,IF(G10="18TB S5 TBR",Datto!I38,IF(G10="24TB S5 TBR",Datto!I39,IF(G10="36TB S5 TBR",Datto!I40,IF(G10="48TB S5 TBR",Datto!I41,IF(G10="60TB S5 TBR",Datto!I42,IF(G10="1 TB Virt TBR",Datto!I43,IF(G10="2 TB Virt TBR",Datto!I44,IF(G10="3 TB Virt TBR",Datto!I45,IF(G10="4 TB Virt TBR",Datto!I46,IF(G10="6 TB Virt TBR",Datto!I47,IF(G10="8 TB Virt TBR",Datto!I48,IF(G10="12 TB Virt TBR",Datto!I49,IF(G10="18 TB Virt TBR",Datto!I50,IF(G10="24 TB Virt TBR",Datto!I51,IF(G10="36 TB Virt TBR",Datto!I52,IF(G10="48 TB Virt TBR",Datto!I53,IF(G10="60 TB Virt TBR",Datto!I54,IF(G10="80 TB Virt TBR",Datto!I55,IF(G10="100 TB Virt TBR",Datto!I56),IF(G10="None","No Datto",IF(G10="2TB S5X ICR",Datto!A5,IF(G10="2TB S5 ICR",Datto!A6,IF(G10="3TB S5 ICR",Datto!A7,IF(G10="4TB S5 ICR",Datto!A8,IF(G10="6TB S5 ICR",Datto!A9,IF(G10="8TB S5 ICR",Datto!A10,IF(G10="12TB S5 ICR",Datto!A11,IF(G10="18TB S5 ICR",Datto!A12,IF(G10="24TB S5 ICR",Datto!A13,IF(G10="36TB S5 ICR",Datto!A14,IF(G10="48TB S5 ICR",Datto!A15,IF(G10="60TB S5 ICR",Datto!A16,IF(G10="1 TB Virt ICR",Datto!A17,IF(G10="2 TB Virt ICR",Datto!A18,IF(G10="3 TB Virt ICR",Datto!A19,IF(G10="4 TB Virt ICR",Datto!A20,IF(G10="6 TB Virt ICR",Datto!A21,IF(G10="8 TB Virt ICR",Datto!A22,IF(G10="12 TB Virt ICR",Datto!A23,IF(G10="18 TB Virt ICR",Datto!A24,IF(G10="24 TB Virt ICR",Datto!A25,IF(G10="36 TB Virt ICR",Datto!A26,IF(G10="48 TB Virt ICR",Datto!A27,IF(G10="60 TB Virt ICR",Datto!A28,IF(G10="80 TB Virt ICR",Datto!A29,IF(G10="100 TB Virt ICR",Datto!A30,IF(G10="2TB S5X TBR",Datto!A31,IF(G10="2TB S5 TBR",Datto!A32,IF(G10="3TB S5 TBR",Datto!A33,IF(G10="4TB S5 TBR",Datto!A34,IF(G10="6TB S5 TBR",Datto!A35,IF(G10="8TB S5 TBR",Datto!A36,IF(G10="12TB S5 TBR",Datto!A37,IF(G10="18TB S5 TBR",Datto!A38,IF(G10="24TB S5 TBR",Datto!A39,IF(G10="36TB S5 TBR",Datto!A40,IF(G10="48TB S5 TBR",Datto!A41,IF(G10="60TB S5 TBR",Datto!A42,IF(G10="1 TB Virt TBR",Datto!A43,IF(G10="2 TB Virt TBR",Datto!A44,IF(G10="3 TB Virt TBR",Datto!A45,IF(G10="4 TB Virt TBR",Datto!A46,IF(G10="6 TB Virt TBR",Datto!A47,IF(G10="8 TB Virt TBR",Datto!A48,IF(G10="12 TB Virt TBR",Datto!A49,IF(G10="18 TB Virt TBR",Datto!A50,IF(G10="24 TB Virt TBR",Datto!A51,IF(G10="36 TB Virt TBR",Datto!A52,IF(G10="48 TB Virt TBR",Datto!A53,IF(G10="60 TB Virt TBR",Datto!A54,IF(G10="80 TB Virt TBR",Datto!A55,IF(G10="100 TB Virt TBR",Datto!A56)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

[#12]

@dbrauss 

1) Why do all conditions such as G10="None" occur twice?

2) Why does the cell to return jump from G4 ... G30 to I31:I56?

I'd enter the values None, 2TB S5X ICR, ..., 100 TB VIRT TBR in a column on the Datto sheet starting in row 4, for example in B4:B56.

You can then use

=XLOOKUP(G10, Datto!$B$4:$B$56, Datto!$A$4:$A$56, "")

[#13]

@dbrauss 

Create a lookup table and use XLOOKUP.

For example,

[#14]

Need for more than 64 levels of nested - Simplify formula -
Here's is my formula, its for a quote, for cleaning business,
For type of cleaning: Standard, Deep or Complete, with the selection of how many Square foot, and the price And form 1 to 5x a week, my code here is only up to 3x a week as its so long..
It's a very long one and hope there's someone can help me simplify it. Thanks in advance!

=IF(AND(B7="Standard",C8=2000,E7="1X a week"),I9,IF(AND(B7="Standard",C8=2500,E7="1X a week"),I10,
IF(AND(B7="Standard",C8=3000,E7="1X a week"),I11,IF(AND(B7="Standard",C8=3500,E7="1X a week"),I12,
IF(AND(B7="Standard",C8=4000,E7="1X a week"),I13,IF(AND(B7="Standard",C8=4500,E7="1X a week"),I14,
IF(AND(B7="Standard",C8=5000,E7="1X a week"),I15,IF(AND(B7="Standard",C8=5500,E7="1X a week"),I16,
IF(AND(B7="Standard",C8=6000,E7="1X a week"),I17,IF(AND(B7="Standard",C8=6500,E7="1X a week"),I18,
IF(AND(B7="Standard",C8=7000,E7="1X a week"),I19,IF(AND(B7="Standard",C8=7500,E7="1X a week"),I20,
IF(AND(B7="Standard",C8=8000,E7="1X a week"),I21,IF(AND(B7="Standard",C8=8500,E7="1X a week"),I21,
IF(AND(B7="Standard",C8=9000,E7="1X a week"),I22,IF(AND(B7="Standard",C8=9500,E7="1X a week"),I23,
IF(AND(B7="Standard",C8=10000,E7="1X a week"),I24,IF(AND(B7="Standard",C8=10500,E7="1X a week"),I25,
IF(AND(B7="Standard",C8=11000,E7="1X a week"),I26,IF(AND(B7="Standard",C8=11500,E7="1X a week"),I27,
IF(AND(B7="Standard",C8=12000,E7="1X a week"),I28,IF(AND(B7="Standard",C8=12500,E7="1X a week"),I29,
IF(AND(B7="Standard",C8=13000,E7="1X a week"),I30,IF(AND(B7="Standard",C8=13500,E7="1X a week"),I31,
IF(AND(B7="Standard",C8=14000,E7="1X a week"),I32,IF(AND(B7="Standard",C8=14500,E7="1X a week"),I33,
IF(AND(B7="Standard",C8=15000,E7="1X a week"),I33,IF(AND(B7="Standard",C8=15500,E7="1X a week"),I34,
IF(AND(B7="Standard",C8=15500,E7="1X a week"),I35,IF(AND(B7="Standard",C8=16000,E7="1X a week"),I36,
IF(AND(B7="Standard",C8=16500,E7="1X a week"),I37,IF(AND(B7="Standard",C8=17000,E7="1X a week"),I38,
IF(AND(B7="Standard",C8=17500,E7="1X a week"),I39,IF(AND(B7="Standard",C8=18000,E7="1X a week"),I40,
IF(AND(B7="Standard",C8=18500,E7="1X a week"),I41,IF(AND(B7="Standard",C8=19000,E7="1X a week"),I42,
IF(AND(B7="Standard",C8=19500,E7="1X a week"),I43,IF(AND(B7="Standard",C8=20000,E7="1X a week"),I44,
IF(AND(B7="Deep",C8=2000,E7="1X a week"),J9,IF(AND(B7="Deep",C8=2500,E7="1X a week"),J10,
IF(AND(B7="Deep",C8=3000,E7="1X a week"),J11,IF(AND(B7="Deep",C8=3500,E7="1X a week"),J12,
IF(AND(B7="Deep",C8=4000,E7="1X a week"),J13,IF(AND(B7="Deep",C8=4500,E7="1X a week"),J14,
IF(AND(B7="Deep",C8=5000,E7="1X a week"),J15,IF(AND(B7="Deep",C8=5500,E7="1X a week"),J16,
IF(AND(B7="Deep",C8=6000,E7="1X a week"),J17,IF(AND(B7="Deep",C8=6500,E7="1X a week"),J18,
IF(AND(B7="Deep",C8=7000,E7="1X a week"),J19,IF(AND(B7="Deep",C8=7500,E7="1X a week"),J20,
IF(AND(B7="Deep",C8=8000,E7="1X a week"),J21,IF(AND(B7="Deep",C8=8500,E7="1X a week"),J21,
IF(AND(B7="Deep",C8=9000,E7="1X a week"),J22,IF(AND(B7="Deep",C8=9500,E7="1X a week"),J23,
IF(AND(B7="Deep",C8=10000,E7="1X a week"),J24,IF(AND(B7="Deep",C8=10500,E7="1X a week"),J25,
IF(AND(B7="Deep",C8=11000,E7="1X a week"),J26,IF(AND(B7="DeepC8=11500,E7="1X a week"),J27,
IF(AND(B7="Deep",C8=12000,E7="1X a week"),J28,IF(AND(B7="Deep",C8=12500,E7="1X a week"),J29,
IF(AND(B7="Deep",C8=13000,E7="1X a week"),J30,IF(AND(B7="Deep",C8=13500,E7="1X a week"),J31,
IF(AND(B7="Deep",C8=14000,E7="1X a week"),J32,IF(AND(B7="Deep",C8=14500,E7="1X a week"),J33,
IF(AND(B7="Deep",C8=15000,E7="1X a week"),J33,IF(AND(B7="Deep",C8=15500,E7="1X a week"),J34,
IF(AND(B7="Deep",C8=15500,E7="1X a week"),J35,IF(AND(B7="Deep",C8=16000,E7="1X a week"),J36,
IF(AND(B7="Deep",C8=16500,E7="1X a week"),J37,IF(AND(B7="Deep",C8=17000,E7="1X a week"),J38,
IF(AND(B7="Deep",C8=17500,E7="1X a week"),J39,IF(AND(B7="Deep",C8=18000,E7="1X a week"),J40,
IF(AND(B7="Deep",C8=18500,E7="1X a week"),J41,IF(AND(B7="Deep",C8=19000,E7="1X a week"),J42,
IF(AND(B7="Deep",C8=19500,E7="1X a week"),J43,IF(AND(B7="Deep",C8=20000,E7="1X a week"),J44,
IF(AND(B7="Complete",C8=2000,E7="1X a week"),K9,IF(AND(B7=" Complete ",C8=2500,E7="1X a week"),K10,
IF(AND(B7=" Complete ",C8=3000,E7="1X a week"),K11,IF(AND(B7=" Complete ",C8=3500,E7="1X a week"),K12,
IF(AND(B7=" Complete ",C8=4000,E7="1X a week"),K13,IF(AND(B7=" Complete ",C8=4500,E7="1X a week"),K14,
IF(AND(B7=" Complete ",C8=5000,E7="1X a week"),K15,IF(AND(B7=" Complete ",C8=5500,E7="1X a week"),K16,
IF(AND(B7=" Complete ",C8=6000,E7="1X a week"),K17,IF(AND(B7=" Complete ",C8=6500,E7="1X a week"),K18,
IF(AND(B7=" Complete ",C8=7000,E7="1X a week"),K19,IF(AND(B7=" Complete ",C8=7500,E7="1X a week"),K20,
IF(AND(B7=" Complete ",C8=8000,E7="1X a week"),K21,IF(AND(B7=" Complete ",C8=8500,E7="1X a week"),K21,
IF(AND(B7=" Complete ",C8=9000,E7="1X a week"),K22,IF(AND(B7=" Complete ",C8=9500,E7="1X a week"),K23,
IF(AND(B7=" Complete ",C8=10000,E7="1X a week"),K24,IF(AND(B7=" Complete ",C8=10500,E7="1X a week"),K25,
IF(AND(B7=" Complete ",C8=11000,E7="1X a week"),K26,IF(AND(B7=" Complete ",C8=11500,E7="1X a week"),K27,
IF(AND(B7=" Complete ",C8=12000,E7="1X a week"),K28,IF(AND(B7=" Complete ",C8=12500,E7="1X a week"),K29,
IF(AND(B7=" Complete ",C8=13000,E7="1X a week"),K30,IF(AND(B7=" Complete ",C8=13500,E7="1X a week"),K31,
IF(AND(B7=" Complete ",C8=14000,E7="1X a week"),K32,IF(AND(B7=" Complete ",C8=14500,E7="1X a week"),K33,
IF(AND(B7=" Complete ",C8=15000,E7="1X a week"),K33,IF(AND(B7=" Complete ",C8=15500,E7="1X a week"),K34,
IF(AND(B7=" Complete ",C8=15500,E7="1X a week"),K35,IF(AND(B7=" Complete ",C8=16000,E7="1X a week"),K36,
IF(AND(B7=" Complete ",C8=16500,E7="1X a week"),K37,IF(AND(B7=" Complete ",C8=17000,E7="1X a week"),K38,
IF(AND(B7=" Complete ",C8=17500,E7="1X a week"),K39,IF(AND(B7=" Complete ",C8=18000,E7="1X a week"),K40,
IF(AND(B7=" Complete ",C8=18500,E7="1X a week"),K41,IF(AND(B7=" Complete ",C8=19000,E7="1X a week"),K42,
IF(AND(B7=" Complete ",C8=19500,E7="1X a week"),K43,IF(AND(B7=" Complete ",C8=20000,E7="1X a week"),K44,

IF(AND(B7="Standard",C8=2000,E7="2X a week"),M9,IF(AND(B7="Standard",C8=2500,E7="2X a week"),M10,
IF(AND(B7="Standard",C8=3000,E7="2X a week"),M11,IF(AND(B7="Standard",C8=3500,E7="2X a week"),M12,
IF(AND(B7="Standard",C8=4000,E7="2X a week"),M13,IF(AND(B7="Standard",C8=4500,E7="2X a week"),M14,
IF(AND(B7="Standard",C8=5000,E7="2X a week"),M15,IF(AND(B7="Standard",C8=5500,E7="2X a week"),M16,
IF(AND(B7="Standard",C8=6000,E7="2X a week"),M17,IF(AND(B7="Standard",C8=6500,E7="2X a week"),M18,
IF(AND(B7="Standard",C8=7000,E7="2X a week"),M19,IF(AND(B7="Standard",C8=7500,E7="2X a week"),M20,
IF(AND(B7="Standard",C8=8000,E7="2X a week"),M21,IF(AND(B7="Standard",C8=8500,E7="2X a week"),M21,
IF(AND(B7="Standard",C8=9000,E7="2X a week"),M22,IF(AND(B7="Standard",C8=9500,E7="2X a week"),IM23,
IF(AND(B7="Standard",C8=10000,E7="2X a week"),M24,IF(AND(B7="Standard",C8=10500,E7="2X a week"),M25,
IF(AND(B7="Standard",C8=11000,E7="2X a week"),M26,IF(AND(B7="Standard",C8=11500,E7="2X a week"),M27,
IF(AND(B7="Standard",C8=12000,E7="2X a week"),M28,IF(AND(B7="Standard",C8=12500,E7="2X a week"),M29,
IF(AND(B7="Standard",C8=13000,E7="2X a week"),M30,IF(AND(B7="Standard",C8=13500,E7="2X a week"),M31,
IF(AND(B7="Standard",C8=14000,E7="2X a week"),M32,IF(AND(B7="Standard",C8=14500,E7="2X a week"),M33,
IF(AND(B7="Standard",C8=15000,E7="2X a week"),M33,IF(AND(B7="Standard",C8=15500,E7="2X a week"),M34,
IF(AND(B7="Standard",C8=15500,E7="2X a week"),M35,IF(AND(B7="Standard",C8=16000,E7="2X a week"),M36,
IF(AND(B7="Standard",C8=16500,E7="2X a week"),M37,IF(AND(B7="Standard",C8=17000,E7="2X a week"),M38,
IF(AND(B7="Standard",C8=17500,E7="2X a week"),M39,IF(AND(B7="Standard",C8=18000,E7="2X a week"),M40,
IF(AND(B7="Standard",C8=18500,E7="2X a week"),M41,IF(AND(B7="Standard",C8=19000,E7="2X a week"),M42,
IF(AND(B7="Standard",C8=19500,E7="2X a week"),M43,IF(AND(B7="Standard",C8=20000,E7="2X a week"),M44,
IF(AND(B7="Deep",C8=2000,E7="2X a week"),N9,IF(AND(B7="Deep",C8=2500,E7="2X a week"),N10,
IF(AND(B7="Deep",C8=3000,E7="2X a week"),N11,IF(AND(B7="Deep",C8=3500,E7="2X a week"),N12,
IF(AND(B7="Deep",C8=4000,E7="2X a week"),N13,IF(AND(B7="Deep",C8=4500,E7="2X a week"),N14,
IF(AND(B7="Deep",C8=5000,E7="2X a week"),N15,IF(AND(B7="Deep",C8=5500,E7="2X a week"),N16,
IF(AND(B7="Deep",C8=6000,E7="2X a week"),N17,IF(AND(B7="Deep",C8=6500,E7="2X a week"),N18,
IF(AND(B7="Deep",C8=7000,E7="2X a week"),N19,IF(AND(B7="Deep",C8=7500,E7="2X a week"),N20,
IF(AND(B7="Deep",C8=8000,E7="2X a week"),N21,IF(AND(B7="Deep",C8=8500,E7="2X a week"),N21,
IF(AND(B7="Deep",C8=9000,E7="2X a week"),N22,IF(AND(B7="Deep",C8=9500,E7="2X a week"),JN23,
IF(AND(B7="Deep",C8=10000,E7="2X a week"),N24,IF(AND(B7="Deep",C8=10500,E7="2X a week"),N25,
IF(AND(B7="Deep",C8=11000,E7="2X a week"),N26,IF(AND(B7="DeepC8=11500,E7="2X a week"),N27,
IF(AND(B7="Deep",C8=12000,E7="2X a week"),N28,IF(AND(B7="Deep",C8=12500,E7="2X a week"),N29,
IF(AND(B7="Deep",C8=13000,E7="2X a week"),N30,IF(AND(B7="Deep",C8=13500,E7="2X a week"),N31,
IF(AND(B7="Deep",C8=14000,E7="2X a week"),N32,IF(AND(B7="Deep",C8=14500,E7="2X a week"),N33,
IF(AND(B7="Deep",C8=15000,E7="2X a week"),N33,IF(AND(B7="Deep",C8=15500,E7="2X a week"),N34,
IF(AND(B7="Deep",C8=15500,E7="2X a week"),N35,IF(AND(B7="Deep",C8=16000,E7="2X a week"),N36,
IF(AND(B7="Deep",C8=16500,E7="2X a week"),N37,IF(AND(B7="Deep",C8=17000,E7="2X a week"),N38,
IF(AND(B7="Deep",C8=17500,E7="2X a week"),N39,IF(AND(B7="Deep",C8=18000,E7="1X a week"),N40,
IF(AND(B7="Deep",C8=18500,E7="2X a week"),N41,IF(AND(B7="Deep",C8=19000,E7="2X a week"),N42,
IF(AND(B7="Deep",C8=19500,E7="2X a week"),N43,IF(AND(B7="Deep",C8=20000,E7="1X a week"),N44,
IF(AND(B7="Complete",C8=2000,E7="2X a week"),O9,IF(AND(B7=" Complete ",C8=2500,E7="1X a week"),O10,
IF(AND(B7=" Complete ",C8=3000,E7="2X a week"),O11,IF(AND(B7=" Complete ",C8=3500,E7="2X a week"),O12,
IF(AND(B7=" Complete ",C8=4000,E7="2X a week"),O13,IF(AND(B7=" Complete ",C8=4500,E7="2X a week"),O14,
IF(AND(B7=" Complete ",C8=5000,E7="2X a week"),O15,IF(AND(B7=" Complete ",C8=5500,E7="2X a week"),O16,
IF(AND(B7=" Complete ",C8=6000,E7="2X a week"),O17,IF(AND(B7=" Complete ",C8=6500,E7="2X a week"),O18,
IF(AND(B7=" Complete ",C8=7000,E7="2X a week"),O19,IF(AND(B7=" Complete ",C8=7500,E7="2X a week"),O20,
IF(AND(B7=" Complete ",C8=8000,E7="2X a week"),O21,IF(AND(B7=" Complete ",C8=8500,E7="2X a week"),O21,
IF(AND(B7=" Complete ",C8=9000,E7="2X a week"),O22,IF(AND(B7=" Complete ",C8=9500,E7="2X a week"),O23,
IF(AND(B7=" Complete ",C8=10000,E7="2X a week"),O24,IF(AND(B7=" Complete ",C8=10500,E7="2X a week"),O25,
IF(AND(B7=" Complete ",C8=11000,E7="2X a week"),O26,IF(AND(B7=" Complete ",C8=11500,E7="2X a week"),O27,
IF(AND(B7=" Complete ",C8=12000,E7="2X a week"),O28,IF(AND(B7=" Complete ",C8=12500,E7="2X a week"),O29,
IF(AND(B7=" Complete ",C8=13000,E7="2X a week"),O30,IF(AND(B7=" Complete ",C8=13500,E7="2X a week"),O31,
IF(AND(B7=" Complete ",C8=14000,E7="2X a week"),O32,IF(AND(B7=" Complete ",C8=14500,E7="2X a week"),O33,
IF(AND(B7=" Complete ",C8=15000,E7="2X a week"),O33,IF(AND(B7=" Complete ",C8=15500,E7="2X a week"),O34,
IF(AND(B7=" Complete ",C8=15500,E7="2X a week"),O35,IF(AND(B7=" Complete ",C8=16000,E7="2X a week"),O36,
IF(AND(B7=" Complete ",C8=16500,E7="2X a week"),O37,IF(AND(B7=" Complete ",C8=17000,E7="2X a week"),O38,
IF(AND(B7=" Complete ",C8=17500,E7="2X a week"),O39,IF(AND(B7=" Complete ",C8=18000,E7="2X a week"),O40,
IF(AND(B7=" Complete ",C8=18500,E7="2X a week"),O41,IF(AND(B7=" Complete ",C8=19000,E7="2X a week"),O42,
IF(AND(B7=" Complete ",C8=19500,E7="2X a week"),O43,IF(AND(B7=" Complete ",C8=20000,E7="2X a week"),O44,

IF(AND(B7="Standard",C8=2000,E7="3X a week"),Q9,IF(AND(B7="Standard",C8=2500,E7="1X a week"),Q10,
IF(AND(B7="Standard",C8=3000,E7="3X a week"),Q11,IF(AND(B7="Standard",C8=3500,E7="1X a week"),Q12,
IF(AND(B7="Standard",C8=4000,E7="3X a week"),Q13,IF(AND(B7="Standard",C8=4500,E7="1X a week"),Q14,
IF(AND(B7="Standard",C8=5000,E7="3X a week"),Q15,IF(AND(B7="Standard",C8=5500,E7="1X a week"),Q16,
IF(AND(B7="Standard",C8=6000,E7="3X a week"),Q17,IF(AND(B7="Standard",C8=6500,E7="1X a week"),Q18,
IF(AND(B7="Standard",C8=7000,E7="3X a week"),Q19,IF(AND(B7="Standard",C8=7500,E7="1X a week"),Q20,
IF(AND(B7="Standard",C8=8000,E7="3X a week"),Q21,IF(AND(B7="Standard",C8=8500,E7="1X a week"),Q21,
IF(AND(B7="Standard",C8=9000,E7="3X a week"),Q22,IF(AND(B7="Standard",C8=9500,E7="1X a week"),Q23,
IF(AND(B7="Standard",C8=10000,E7="3X a week"),Q24,IF(AND(B7="Standard",C8=10500,E7="1X a week"),Q25,
IF(AND(B7="Standard",C8=11000,E7="3X a week"),Q26,IF(AND(B7="Standard",C8=11500,E7="1X a week"),Q27,
IF(AND(B7="Standard",C8=12000,E7="3X a week"),Q28,IF(AND(B7="Standard",C8=12500,E7="1X a week"),Q29,
IF(AND(B7="Standard",C8=13000,E7="3X a week"),Q30,IF(AND(B7="Standard",C8=13500,E7="1X a week"),Q31,
IF(AND(B7="Standard",C8=14000,E7="3X a week"),Q32,IF(AND(B7="Standard",C8=14500,E7="1X a week"),Q33,
IF(AND(B7="Standard",C8=15000,E7="3X a week"),Q33,IF(AND(B7="Standard",C8=15500,E7="1X a week"),Q34,
IF(AND(B7="Standard",C8=15500,E7="3X a week"),Q35,IF(AND(B7="Standard",C8=16000,E7="1X a week"),Q36,
IF(AND(B7="Standard",C8=16500,E7="3X a week"),Q37,IF(AND(B7="Standard",C8=17000,E7="1X a week"),Q38,
IF(AND(B7="Standard",C8=17500,E7="3X a week"),Q39,IF(AND(B7="Standard",C8=18000,E7="1X a week"),Q40,
IF(AND(B7="Standard",C8=18500,E7="3X a week"),Q41,IF(AND(B7="Standard",C8=19000,E7="1X a week"),Q42,
IF(AND(B7="Standard",C8=19500,E7="3X a week"),Q43,IF(AND(B7="Standard",C8=20000,E7="1X a week"),Q44,
IF(AND(B7="Deep",C8=2000,E7="3X a week"),R9,IF(AND(B7="Deep",C8=2500,E7="3X a week"),R10,
IF(AND(B7="Deep",C8=3000,E7="3X a week"),R11,IF(AND(B7="Deep",C8=3500,E7="3X a week"),R12,
IF(AND(B7="Deep",C8=4000,E7="3X a week"),R13,IF(AND(B7="Deep",C8=4500,E7="3X a week"),R14,
IF(AND(B7="Deep",C8=5000,E7="3X a week"),R15,IF(AND(B7="Deep",C8=5500,E7="3X a week"),R16,
IF(AND(B7="Deep",C8=6000,E7="3X a week"),R17,IF(AND(B7="Deep",C8=6500,E7="3X a week"),R18,
IF(AND(B7="Deep",C8=7000,E7="3X a week"),R19,IF(AND(B7="Deep",C8=7500,E7="3X a week"),R20,
IF(AND(B7="Deep",C8=8000,E7="3X a week"),R21,IF(AND(B7="Deep",C8=8500,E7="3X a week"),R21,
IF(AND(B7="Deep",C8=9000,E7="3X a week"),R22,IF(AND(B7="Deep",C8=9500,E7="3X a week"),R23,
IF(AND(B7="Deep",C8=10000,E7="3X a week"),R24,IF(AND(B7="Deep",C8=10500,E7="3X a week"),R25,
IF(AND(B7="Deep",C8=11000,E7="3X a week"),R26,IF(AND(B7="DeepC8=11500,E7="3X a week"),R27,
IF(AND(B7="Deep",C8=12000,E7="3X a week"),R28,IF(AND(B7="Deep",C8=12500,E7="3X a week"),R29,
IF(AND(B7="Deep",C8=13000,E7="3X a week"),R30,IF(AND(B7="Deep",C8=13500,E7="3X a week"),R31,
IF(AND(B7="Deep",C8=14000,E7="3X a week"),R32,IF(AND(B7="Deep",C8=14500,E7="3X a week"),R33,
IF(AND(B7="Deep",C8=15000,E7="3X a week"),R33,IF(AND(B7="Deep",C8=15500,E7="3X a week"),R34,
IF(AND(B7="Deep",C8=15500,E7="3X a week"),R35,IF(AND(B7="Deep",C8=16000,E7="3X a week"),R36,
IF(AND(B7="Deep",C8=16500,E7="3X a week"),R37,IF(AND(B7="Deep",C8=17000,E7="3X a week"),R38,
IF(AND(B7="Deep",C8=17500,E7="3X a week"),R39,IF(AND(B7="Deep",C8=18000,E7="3X a week"),R40,
IF(AND(B7="Deep",C8=18500,E7="3X a week"),R41,IF(AND(B7="Deep",C8=19000,E7="3X a week"),R42,
IF(AND(B7="Deep",C8=19500,E7="3X a week"),R43,IF(AND(B7="Deep",C8=20000,E7="3X a week"),R44,
IF(AND(B7="Complete",C8=2000,E7="3X a week"),S9,IF(AND(B7=" Complete ",C8=2500,E7="3X a week"),S10,
IF(AND(B7=" Complete ",C8=3000,E7="3X a week"),S11,IF(AND(B7=" Complete ",C8=3500,E7="3X a week"),S12,
IF(AND(B7=" Complete ",C8=4000,E7="3X a week"),S13,IF(AND(B7=" Complete ",C8=4500,E7="3X a week"),S14,
IF(AND(B7=" Complete ",C8=5000,E7="3X a week"),S15,IF(AND(B7=" Complete ",C8=5500,E7="3X a week"),S16,
IF(AND(B7=" Complete ",C8=6000,E7="3X a week"),S17,IF(AND(B7=" Complete ",C8=6500,E7="3X a week"),S18,
IF(AND(B7=" Complete ",C8=7000,E7="3X a week"),S19,IF(AND(B7=" Complete ",C8=7500,E7="3X a week"),S20,
IF(AND(B7=" Complete ",C8=8000,E7="3X a week"),S21,IF(AND(B7=" Complete ",C8=8500,E7="3X a week"),S21,
IF(AND(B7=" Complete ",C8=9000,E7="3X a week"),S22,IF(AND(B7=" Complete ",C8=9500,E7="3X a week"),S23,
IF(AND(B7=" Complete ",C8=10000,E7="3X a week"),S24,IF(AND(B7=" Complete ",C8=10500,E7="3X a week"),S25,
IF(AND(B7=" Complete ",C8=11000,E7="3X a week"),S26,IF(AND(B7=" Complete ",C8=11500,E7="3X a week"),S27,
IF(AND(B7=" Complete ",C8=12000,E7="3X a week"),S28,IF(AND(B7=" Complete ",C8=12500,E7="3X a week"),S29,
IF(AND(B7=" Complete ",C8=13000,E7="3X a week"),S30,IF(AND(B7=" Complete ",C8=13500,E7="3X a week"),S31,
IF(AND(B7=" Complete ",C8=14000,E7="3X a week"),S32,IF(AND(B7=" Complete ",C8=14500,E7="3X a week"),S33,
IF(AND(B7=" Complete ",C8=15000,E7="3X a week"),S33,IF(AND(B7=" Complete ",C8=15500,E7="3X a week"),S34,
IF(AND(B7=" Complete ",C8=15500,E7="3X a week"),S35,IF(AND(B7=" Complete ",C8=16000,E7="3X a week"),S36,
IF(AND(B7=" Complete ",C8=16500,E7="3X a week"),S37,IF(AND(B7=" Complete ",C8=17000,E7="3X a week"),S38,
IF(AND(B7=" Complete ",C8=17500,E7="3X a week"),S39,IF(AND(B7=" Complete ",C8=18000,E7="3X a week"),S40,
IF(AND(B7=" Complete ",C8=18500,E7="3X a week"),S41,IF(AND(B7=" Complete ",C8=19000,E7="3X a week"),S42,
IF(AND(B7=" Complete ",C8=19500,E7="3X a week"),S43,IF(AND(B7=" Complete ",C8=20000,E7="3X a week"),S44,
""))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

[#15]

Please help me simplify following formula. The formula is designed to return specific date, in this case if a date falls in January 2017 it will return 20th of next month and if I write it for 5-6 years it exceeds its limit of 64 nest. Is there any way to simplify this formula?? IF(AND(C189>DATEVALUE("31/12/2017"),C189DATEVALUE("31/01/2018"),C189DATEVALUE("28/02/2018"),C189DATEVALUE("31/03/2018"),C189DATEVALUE("30/04/2018"),C189DATEVALUE("31/05/2018"),C189DATEVALUE("30/06/2018"),C189DATEVALUE("31/07/2018"),C189DATEVALUE("31/08/2018"),C189DATEVALUE("30/09/2018"),C189DATEVALUE("31/10/2018"),C189DATEVALUE("30/11/2018"),C189

[#16]

@Vrushaket 

=EOMONTH(C189, 0)+20

[#17]

@Hans Vogelaar Thank you 🙏🏻🙏🏻👍🏻:smiling_face_with_smiling_eyes: