Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Copilot for Microsoft 365 Tech Accelerator

Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)

Microsoft Tech Community

SOLVED
Home
#
Need for more than 64 levels of nested - Simplify formula

- Home
- Microsoft 365
- Excel
- Need for more than 64 levels of nested - Simplify formula

- Subscribe to RSS Feed
- Mark Discussion as New
- Mark Discussion as Read
- Pin this Discussion for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 06 2020 07:08 AM

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,

Labels:

35 Replies

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 06 2020 07:40 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 06 2020 07:51 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 06 2020 08:54 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 06 2020 09:25 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 06 2020 10:42 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 06 2020 01:08 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 06 2020 02:27 PM

@Nikoss

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

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 06 2020 03:57 PM - edited Nov 06 2020 03:58 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 09 2020 10:20 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 09 2020 10:26 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 09 2020 10:38 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 09 2020 11:08 AM

@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")`

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 09 2020 11:35 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 09 2020 11:43 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 09 2020 12:04 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 09 2020 12:17 PM

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

best response confirmed by
Nikoss* (Copper Contributor)*

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 09 2020 12:46 PM

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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 09 2020 12:49 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 09 2020 01:04 PM

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

1 best response

Accepted Solutions

best response confirmed by
Nikoss* (Copper Contributor)*

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 09 2020 12:46 PM

Solution