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]

Hi @Nikoss,

This is indeed a very long formula and as you said, there's got to be a way to make it shorter.

Can you share your spreadsheet or at least part of it to better assist you? Make sure there's no confidential data. I'm especially interested in the 'Pricing Matrix' sheet. 

[#3]

@Bennadeau Thank you for the prompt response. I have attached a screenshot of it. The values on Column D and E are supposed to be prices but because that is confidential I have replaced them. I hope that helps.

[#4]

@Nikoss 

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.

[#5]

@Bennadeau unfortunately I get a #VALUE error with this formula

[#6]

@Nikoss 

There was a small error in my original formula. Try this one.

=IF(EW2<INDEX('Pricing Matrix'!D:D,MATCH(G4&I4,'Pricing Matrix'!B:B&'Pricing Matrix'!C:C,0)),"Below",IF(AND(EW2>=INDEX('Pricing Matrix'!D:D,MATCH(G4&I4,'Pricing Matrix'!B:B&'Pricing Matrix'!C:C,0)),EW2<=INDEX('Pricing Matrix'!E:E,MATCH(G4&I4,'Pricing Matrix'!B:B&'Pricing Matrix'!C:C,0))),"In accordance with Pricing Matrix",IF(EW2>INDEX('Pricing Matrix'!E:E,MATCH(G4&I4,'Pricing Matrix'!B:B&'Pricing Matrix'!C:C,0)),"Over","NA")))

I also attached a sample workbook. Cell L4 is where the formula is. Perhaps I missed something in your explanation so you can see how I based the formula?

I see in your formula that you reference cell G4 for the date and I4 for the model but suddenly, it jumps to cell G2 and I2. I assumed this was a mistake in your formula, let me know if I'm wrong there.

[#7]

@Bennadeau yes that was a mistake from my end, which I noticed the moment I posted the question, but I didn't want to make things more complicated, that is why I didn't mention it and corrected the mistake on my end. Maybe there is a mistake on the sample you sent me because I don't see the formula on L4. L4 formula is "=I17" and the result is "0" (since I17 is blank).

I am sure you have understood by now what I am trying to do but just to be 100% sure here is an explanation based on the sample you sent me:

1. If "G4" matches any of the cells from Pricing Matrix Column B, "I4" matches any of the cells from Pricing Matrix Column C and "EW2" is smaller than Pricing Matrix cell "D4" then the result should be "Below"

2. If "G4" matches any of the cells from Pricing Matrix Column B, "I4" matches any of the cells from Pricing Matrix Column C, "EW2" is equal or greater than Pricing Matrix cell "D4" and equal or smaller than Pricing Matrix cell "E4", then the result should be "In Accordance with Pricing Matrix"

3. If "G4" matches any of the cells from Pricing Matrix Column B, "I4" matches any of the cells from Pricing Matrix Column C and "EW2" is greater than Pricing Matrix cell "E4" then the result should be "Over"

I have attached your sample worksheet as well. I have copied and paste your formula on EX2 and as you can see I get the same #VALUE! error.

I really appreciate your help with this @Bennadeau 

Thank you,

[#8]

@Nikoss
Some weird thing happened with that formula. I'm uploading a revised version of the doc. Hopefully it will work fine now.

[#9]

@Nikoss  Alternatively I think this works:

=CHOOSE(IFERROR(SUM(--((FILTER('Pricing Matrix'!D:E,Sheet1!G4&Sheet1!I4='Pricing Matrix'!B:B&'Pricing Matrix'!C:C,"NA")-EW4)>=0))+2,1),"NA","Over","In accordance with Pricing Matrix","Under")

see attached sheet.

Also, you can make use of the IFS() function to eliminate nested IF() statements.

[#10]

@Bennadeau although it works on the sample sheet it doesnt work on my original one for some reason so something is wrong with the original one probably. Again, I would like to thank you for your help on this, it is very much appreciated!

[#11]

@mtarler Thanks a lot for your response. My version of excel doesn't support the "FILTER" function apparently. Would you be kind enough to give me an example of what the formula would look like with IFS function?

[#12]

@Nikoss 

You may want to check to make sure your cell formatting match. 

Number formatting when only numbers are present

Currency when it's a $ amount

Text for everything else.

In both sheets of course. 

Also, ensure there is no trailing space in your cells (white space at the end or "2016" for example). This will mess up things.

[#13]

@Nikoss  Here are 2 versions you can try.  1 using IFS and the other using CHOOSE and SUMPRODUCT:

=IFS(EW2<INDEX('Pricing Matrix'!D:D,MATCH(G4&I4,'Pricing Matrix'!B:B&'Pricing Matrix'!C:C,0)),"Below",EW2<=INDEX('Pricing Matrix'!E:E,MATCH(G4&I4,'Pricing Matrix'!B:B&'Pricing Matrix'!C:C,0)),"In accordance with Pricing Matrix",EW2>INDEX('Pricing Matrix'!E:E,MATCH(G4&I4,'Pricing Matrix'!B:B&'Pricing Matrix'!C:C,0)),"Over",TRUE,"NA")

=CHOOSE(SUMPRODUCT((--('Pricing Matrix'!D:D<=EW4)--('Pricing Matrix'!E:E<EW4)+1)*('Pricing Matrix'!B:B=Sheet1!G4)*('Pricing Matrix'!C:C=Sheet1!I4))+1,"NA","Under","In accordance with Pricing Matrix","Over")

[#14]

@Bennadeau I checked and everything seems fine. I will not give up though and I will find what the problem is.

[#15]

@Nikoss 

If by any chance you could make a copy of that workbook, remove everything confidential in it (actual price, client info if any and so on) and upload it here it would help me help you.

[#16]

@Bennadeau Unfortunately I cannot as it has way too much confidential information and the moment i change it it messes up all the formulas etc.

[#17]

@mtarler Apparently my excel version doesn't support IFS function either. The second formula you sent me though which is supported is giving me the wrong results for some reason even on the sample worksheet you sent me.

[#18 · Accepted solution]

@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?

[#19]

@Bennadeau here is a video of the calculation steps of the formula if that helps somehow: https://photos.app.goo.gl/mQ9gXihuwPtuKxyc9 

[#20]

@mtarler Yesssssss this works!!!!!!!!!!!! Thank you soooo much!!!!! I have been trying to figure this out for almost a week! I really really appreciate your help!