Forum Discussion
Need for more than 64 levels of nested - Simplify formula
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,
Nikoss I and re-attaching the sheet. I deleted the other formulas on that sheet in case there was some confusion. It works just fine for me. Maybe you could explain a little more what you mean by it giving the wrong results. Does it sometimes give correct results? Does it ever change? Can you give an example of a wrong result and what it gave and what you wanted it to give?
35 Replies
- I'm glad we finally got something working for you
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
Try this...
=IF(EW4<INDEX('Pricing Matrix'!D:D,MATCH(G4&I4,'Pricing Matrix'!B:B&'Pricing Matrix'!C:C,0)),"Below",IF(AND(EW4>=INDEX('Pricing Matrix'!D:D,MATCH(G4&I4,'Pricing Matrix'!B:B&'Pricing Matrix'!C:C,0)),EW4<=INDEX('Pricing Matrix'!E:E,MATCH(G4&I4,'Pricing Matrix'!B:B&'Pricing Matrix'!C:C,0))),"In accordance with Pricing Matrix",IF(EW4>INDEX('Pricing Matrix'!E:E,MATCH(G4&I4,'Pricing Matrix'!B:B&'Pricing Matrix'!C:C,0)),"Over","NA")))It is dynamic and flexible as you add more data in your Pricing Matrix sheet.
