Question about Excel Lookup Formulas vs Memory required by Same.

[AllenB]

As Worksheets Grow in Size and Slow Down from Memory Consumption, I wonder if Some Formulas should be Avoided. It seems to Me that Vlookup and HLookup functions are Memory Hogs, especially on large blocks of Data. The Indirect Function using the Address Function, is an alternative that seams to calculate faster. Offset and Index functions also seam quicker. Does anyone here know if these observations are true? Is there a Difference?
I have always liked the Vlookup and Hlookup Functions. They are the easiest to construct. I have heard some concern about the sequence of data needing to be inverted because these functions require ascending values. This can be overcome by using the Negative value of the First Column or Row. For example, In Ca they use Game Numbers as Well as Dates. There is no need to invert the Data, all you have to do is multiply all of the Game Numbers by -1 and you Get the Ascending Values required by the Function. Vlookup Functions can also include Indirect, Offset, And Index Functions to establish the Elements of the Function. How does this affect Memory Use?
Can anyone help or direct me to a reference to this Question?

 

[Frank]

Well, I've never been troubled by speed issues in recent years so I can't say I've noticed any differences. Auto filter on a huge spreadsheet, yes I've noticed. I tend to look at it from other directions, maximising the hardware capabilities of Excel, and minimising redundant formulas. Your calculations will slow up if Excel runs low on memory and uses its page file on the hard drive. You need to avoid this.
Some people may not be aware that even recent releases of 32 bit Excel running on a 32 bit PC can only access 2Gb of memory, which can slow things down on a massive spreadsheet bristling with formulas. So even with 16Gb of installed memory, Excel isn't using it. From Excel 2013 onwards users of 32 bit versions of Excel can take advantage of Large Address Aware functionality after installing the latest updates. This allows 3Gb of memory to be accessed by 32Bit Excel on a 32 bit system. If installed on 64bit machine, 32bit Excel can double its usable memory to 4Gb.
https://support.microsoft.com/en-gb/help/3160741/large-address-aware-capability-change-for-excel


I have both a 64 bit machine and 64 bit Excel on an I7 processor so I can use nearly all my machines memory for Excel, so I'm lucky.
However I don't like redundant formulas, so I copy/paste values to freeze the calculated results of any formulas that will never recalculate new answers. For example a 2000 draws database where each draw had various calculations made on it, odd/even, colour, chi-square, skip per ball, arithmetic complexity, etc.. etc. Ancient draws have the same formulas in them as brand and new draws but do no useful work, so can be replaced by their frozen result. The only thing I have to be wary of are formulas designed to change if ,say I hit a radio button to toggle from "main balls only" to "including bonus ball" which then would require old formulas to spring to life.


Regardless of what the formula is, the less formulas you have, and the less cells referenced by them the faster your formulas will work. Naming formulas saves on memory too and may speed up your calculations. I know my reply is more memory related and doesn't directly address your question but it is interesting to know what parts of Excel are memory hogs and this is an interesting article about this. Conditional formatting and formatting a whole column in entirety are resource hogs for example.


https://support.microsoft.com/en-gb/help/3070372/how-to-clean-up-an-excel-workbook-so-that-it-uses-less-memory.
I did note your comment about VLOOKUP , with Excel there's always more than one way to skin a cat and I don't think that issue is speed related on a small table , but is a valid point on a massive one.