INDEX / MATCH performance for lookups

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

 

[T. Valko]

You can get the answers to all of your questions by using the calculation
timer code provided in that article and experimenting. I do this all the
time!

I just wished that the code was written in such a way that you could select
how many times to run it to get an average and have the times output to a
range of cells rather than a message box. I had made an inquiry about that
but got no response.

But, it's still a very useful tool.

Also, even though the article is about Excel 2007 most of the performace
techniques apply to all versions of Excel.

Biff

 

[VancitysFinest]

I've done what you've said and tried the functions, and now I wonder
how I've gone so long without them!! (slowly I guess)
To answer my own questions:

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.

- No. I think once the MATCH finds the item, it stops looking. I would
hypothesize if there were many items where a MATCH returned #N/A, you
would want to limit your range.

Question #2:
When would I use an array (CTRL-SHIFT-ENTER) when doing and
INDEX(MATCH) lookup? Are there performance benefits?

- None in my case of straight lookups. Anyone know when to use array
formulas?

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?

- Very true. My dataset at the moment has 154 rows with 5 columns
using the same match on a primary key. Converting these 770 matches
down to 154 with a helper column reduced my full workbook calculation
time from 7.8s to 3.9s.

 

[VancitysFinest]

You can get the answers to all of your questions by using the calculation
timer code provided in that article and experimenting. I do this all the
time!

I just wished that the code was written in such a way that you could select
how many times to run it to get an average and have the times output to a
range of cells rather than a message box. I had made an inquiry about that
but got no response.

But, it's still a very useful tool.

Also, even though the article is about Excel 2007 most of the performace
techniques apply to all versions of Excel.

Biff










- Show quoted text -

I've done what you've said and tried the functions, and now I wonder
how I've gone so long without them!! (slowly I guess)
To answer my own questions:

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.

- No. I think once the MATCH finds the item, it stops looking. I would
hypothesize if there were many items where a MATCH returned #N/A, you
would want to limit your range.

Question #2:
When would I use an array (CTRL-SHIFT-ENTER) when doing and
INDEX(MATCH) lookup? Are there performance benefits?

- None in my case of straight lookups. Anyone know when to use array
formulas?

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?

- Very true. My dataset at the moment has 154 rows with 5 columns
using the same match on a primary key. Converting these 770 matches
down to 154 with a helper column reduced my full workbook calculation
time from 7.8s to 3.9s.

 

[VancitysFinest]

You can get the answers to all of your questions by using the calculation
timer code provided in that article and experimenting. I do this all the
time!

I just wished that the code was written in such a way that you could select
how many times to run it to get an average and have the times output to a
range of cells rather than a message box. I had made an inquiry about that
but got no response.

But, it's still a very useful tool.

Also, even though the article is about Excel 2007 most of the performace
techniques apply to all versions of Excel.

Biff

I've done what you've said and tried the functions, and now I wonder
how I've gone so long without them!! (slowly I guess)
To answer my own questions:

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.

- No. I think once the MATCH finds the item, it stops looking. I would
hypothesize if there were many items where a MATCH returned #N/A, you
would want to limit your range.

Question #2:
When would I use an array (CTRL-SHIFT-ENTER) when doing and
INDEX(MATCH) lookup? Are there performance benefits?

- None in my case of straight lookups. Anyone know when to use array
formulas?

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?

- Very true. My dataset at the moment has 154 rows with 5 columns
using the same match on a primary key. Converting these 770 matches
down to 154 with a helper column reduced my full workbook calculation
time from 7.8s to 3.9s.