Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDEX / MATCH performance for lookups
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDEX / MATCH performance for lookups
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" wrote in message oups.com... 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDEX / MATCH performance for lookups
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDEX / MATCH performance for lookups
On Apr 20, 2:46 pm, "T. Valko" wrote:
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" wrote in message oups.com... I was reading this MSDN article on MSDN titled "ImprovingPerformance 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 useINDEX/MATCH, and another 10 do simple mathematical calculations). Question #1: If I do anINDEXorMATCHon a specified range (i.e. A1:A1000) vs the entire column (A:A), is there aperformanceincrease? By how much? And I'd imagine it wouldn't matter what the size of theINDEXfunction 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 thereperformancebenefits? Question #3: The consensus seems to say that separating theMATCHfunction 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.- Hide quoted text - - 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDEX / MATCH performance for lookups
On Apr 20, 1:46 pm, "T. Valko" wrote:
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" wrote in message oups.com... 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. 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
Performance compared to an index using XIRR | Excel Worksheet Functions | |||
lookups and match | Excel Worksheet Functions | |||
SUM of INDEX lookups | Excel Worksheet Functions | |||
Lookups vs Match | Excel Worksheet Functions |