Book using too much memory

[chisox721]

Hi guys, I'm having an issue with the size of my workbook and I was hoping I could get some input on it.

I have a large amount of data on NBA teams and players that is extracted from external web pages. In order to have the data shown on separate sheets to do comparisons I have to use many array formulas and from what I have read that takes up a large amount of memory. I'm now at the point where I'm receiving a message that says "Excel cannot complete this task with available resources" when I open the workbook.

Is there any plausible way that I can resolve this situation? I have thought about converting the lookup formulas from arrays (index/match) to vlookups and hlookups, but I have no idea if that would help at all.

I'm not as advanced with using Excel as most of you, and I realize I may be at a point to where I will need to use different software to analyze my data, but I would prefer to stick with Excel as it would be difficult to start from scratch and learn how to use a different program.

Any thoughts/opinions on my situation would be greatly appreciated.

[TMS]

Are you referring to whole columns in your array formulae?

[chisox721]

Are you referring to whole columns in your array formulae?

Yes! I just did some more research and had an idea that might be what the problem was. I'm now in the process of changing each reference (IE Y:Y to Y3:Y84).

Should that speed things up?

[FDibbins]

I have thought about converting the lookup formulas from arrays (index/match)

the INDEX/MATCH combo is not an array formula by default, and probably would work better h/vlookups. It becomes an array if multiple references are used where a single range would be expected.

=INDEX(A1:E10,match(5,A1:A10,0),match("Feb",A1:E1,1))...is a regular formula
=INDEX(A1:E10,match(5&B1,A1:A10&B1:B10,0),match("Feb",A1:E1,1))...is an array formula

As mentioned above, check for full column/row references and reduce those as much as possible. If you are using array's, maybe consider using helper columns instead