V
VancitysFinest
I was reading this MSDN article on MSDN titled "Improving Performance
in Excel 2007":
http://msdn2.microsoft.com/en-us/library/aa730921.aspx
and it prompted me to optimize my large spreadsheets (1000 rows by 50
columns, about 10 columns use INDEX/MATCH, and another 10 do simple
mathematical calculations).
Question #1:
If I do an INDEX or MATCH on a specified range (i.e. A1:A1000) vs the
entire column (A:A), is there a performance increase? By how much?
And I'd imagine it wouldn't matter what the size of the INDEX function
is since it just takes in a row & column number.
Question #2:
When would I use an array (CTRL-SHIFT-ENTER) when doing and
INDEX(MATCH) lookup? Are there performance benefits?
Question #3:
The consensus seems to say that separating the MATCH function into a
separate column before using it in a complex formula speeds things up.
How true is this?
And unfortunately my work uses Excel 2003, the article is geared
towards Excel 2007, where the new IFERROR function would be of much
use to me.
in Excel 2007":
http://msdn2.microsoft.com/en-us/library/aa730921.aspx
and it prompted me to optimize my large spreadsheets (1000 rows by 50
columns, about 10 columns use INDEX/MATCH, and another 10 do simple
mathematical calculations).
Question #1:
If I do an INDEX or MATCH on a specified range (i.e. A1:A1000) vs the
entire column (A:A), is there a performance increase? By how much?
And I'd imagine it wouldn't matter what the size of the INDEX function
is since it just takes in a row & column number.
Question #2:
When would I use an array (CTRL-SHIFT-ENTER) when doing and
INDEX(MATCH) lookup? Are there performance benefits?
Question #3:
The consensus seems to say that separating the MATCH function into a
separate column before using it in a complex formula speeds things up.
How true is this?
And unfortunately my work uses Excel 2003, the article is geared
towards Excel 2007, where the new IFERROR function would be of much
use to me.