Mutliple Index match in one formula causes memory limit

[olofmur]

Hi,

I have a very long formula that gets the sales value from one of three sheets depending on some conditions. See below formula:

=IFERROR(IF([@[12 months alive(Yes/No)]]="No",IFERROR(INDEX('BUDGET TY'!$Q$1:$Q$5000,MATCH([@[Store '#]],'BUDGET TY'!$B$1:$B$5000,0)),INDEX('BUDGET LY'!$Q$1:$Q$5000,MATCH([@[Store '#]],'BUDGET LY'!$B$1:$B$5000,0))),INDEX('ACTUAL SALES TY'!$B$1:$B$5000,MATCH([@[Store '#]],'ACTUAL SALES TY'!$A$1:$A$5000,0))),"")

So basically, it is checking whether a project has been alive for twelve months and if not it looks for the budgeted sales in two different sheets depending on the year of the budget. If it has been opened for more than twelve months it looks for the store in the actual sales data

The formula works as expected, but my excel has started to complain about memory limits and that it cannot calculate the sheet because of it. I have around 400 projects, and this formula sits in three different columns checking for different things. On top of that, I have around 100 more columns with around 80% being simple one level INDEX MATCH formulas. In total i belive that considering everything I have around 35 000 INDEX MATCH formulas.

Is the memory limit problem caused by these long index match formulas, or is it just to much formulas in the first place? If so, can I use helper columns in some smart way to get rid of the problem?

I keep seeing data sets of hundreds of thousands of rows, while this is merely indexing 5000 rows so i was surprised to get this problem.

Excel 2010 64bit, 8GB RAM

 

[Sinon]

I believe it's the number of cells with formulas that is causing the issue. If you have any cells with formulas that don't really change their value except rarely, I would paste as values instead and reapply formulas as needed. The multi level Index/Match shouldn't cause a problem on its own.

What I do in such cases is to have one row above my data, with just formulas (i hide it so other users can't mess around with it) and that way I can paste as values and apply the formulas from the first row if need be.

Another method would be to set calculations on manual while working in the spreadsheet or maybe just automatic excluding tables as I see you have at least one table set up.

Another thing to try is to remove some formatting like background colours in cells and even conditional formatting if not absolutely necessary. These can cause delays in recalculations. If you have other volatile formulas (see here for which ones those are Volatile Excel Functions -Decision Models) I would dispense with as many as possible as these will recalculate with every single move you make in excel (if you change a cell value, filter a range etc). For instance, if you use TODAY() in every cell it will take longer to recalculate than if you have 1 cell with the function and simply refer to the cell in your other formulas.

You can also try using VLOOKUPS instead of INDEX/MATCH as some believe it's a bit faster but it might not help at all.

 

[olofmur]

Very helpful answer, thank you. I have started to replace with values where I can.

Also, I did have around 2500 formulas with TODAY() in it, so hopefully that speeds things up a little bit. Further, I realized that there are three columns using array INDEX MATCH on multiple criteria. Around 1400 formulas in total, each indexing the same ranges (rows 1 to 5000). Could this be the big bad villain in the drama?

 

[FDibbins]

there are three columns using array INDEX MATCH on multiple criteria.

Make sure those are not using full-column references - rather, restrict them to just the range you need (or a few 000 more). ARRAY formulas are also volatile

 

[olofmur]

Update on this: I could not get it to stop running out of memory. After I converted the table back to a normal range, and the making it a table again the issues stopped. Not sure why, but it worked for me. Thanks for the input!