Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup in large named range
I have a large named range (80 columns, 120 rows). Vlookup hangs (processor
pegged at 100%) now that I have created it. Would speed improve if I split this large range into multiple smaller ones? I am keeping all vlookups within the same worksheet based on advice from an older thread. I also tried Index|Match but it was slower. The overall workbook is less than 1MB and is linked to OLAP. Thanks, Kem |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup in large named range
What's your formula? Is your data sorted?
-- Kind regards, Niek Otten "KemS" wrote in message ... I have a large named range (80 columns, 120 rows). Vlookup hangs (processor pegged at 100%) now that I have created it. Would speed improve if I split this large range into multiple smaller ones? I am keeping all vlookups within the same worksheet based on advice from an older thread. I also tried Index|Match but it was slower. The overall workbook is less than 1MB and is linked to OLAP. Thanks, Kem |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup in large named range
Nick,
The data is not sorted. The formula: =VLOOKUP(Chart!$B$1,EXP,53,FALSE) where Chart1B1 is a data validation drop down cell at the top of the dashboard. The intent is to have the user only see the dashboard page which has 17 graphs. They select the location from the drop down list and see the data result. I suspect from your answer that sorting the data may speed things up. Kind regards, Kem "Niek Otten" wrote: What's your formula? Is your data sorted? -- Kind regards, Niek Otten "KemS" wrote in message ... I have a large named range (80 columns, 120 rows). Vlookup hangs (processor pegged at 100%) now that I have created it. Would speed improve if I split this large range into multiple smaller ones? I am keeping all vlookups within the same worksheet based on advice from an older thread. I also tried Index|Match but it was slower. The overall workbook is less than 1MB and is linked to OLAP. Thanks, Kem |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup in large named range
I tried with 65536 VLOOKUPS, each looking up in 65536 cells; even then I
could hardly notice the calculation time. Something else must be wrong. Visit www.decisionmodels.com for advice -- Kind regards, Niek Otten "KemS" wrote in message ... Nick, The data is not sorted. The formula: =VLOOKUP(Chart!$B$1,EXP,53,FALSE) where Chart1B1 is a data validation drop down cell at the top of the dashboard. The intent is to have the user only see the dashboard page which has 17 graphs. They select the location from the drop down list and see the data result. I suspect from your answer that sorting the data may speed things up. Kind regards, Kem "Niek Otten" wrote: What's your formula? Is your data sorted? -- Kind regards, Niek Otten "KemS" wrote in message ... I have a large named range (80 columns, 120 rows). Vlookup hangs (processor pegged at 100%) now that I have created it. Would speed improve if I split this large range into multiple smaller ones? I am keeping all vlookups within the same worksheet based on advice from an older thread. I also tried Index|Match but it was slower. The overall workbook is less than 1MB and is linked to OLAP. Thanks, Kem |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
dynamic named range function | Excel Worksheet Functions | |||
How do I edit a Named Range using macro's | Excel Worksheet Functions | |||
named range, data validation: list non-selected items, and new added items | Excel Discussion (Misc queries) | |||
How to dynamically reference a dynamic named range | Excel Worksheet Functions | |||
Paste a named range to another range in Excel | Excel Discussion (Misc queries) |