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

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

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

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