Vlookup in large named range

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

KemS - 23 Nov 2005 16:10 GMT

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 to this Message

Niek Otten - 23 Nov 2005 16:31 GMT

What's your formula? Is your data sorted?

Signature

Kind regards,

Niek Otten

>I have a large named range (80 columns, 120 rows). Vlookup hangs
>(processor
[quoted text clipped - 9 lines]
> Thanks,
> Kem

Reply to this Message

KemS - 23 Nov 2005 16:42 GMT

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

> What's your formula? Is your data sorted?
>
[quoted text clipped - 11 lines]
> > Thanks,
> > Kem

Reply to this Message

Niek Otten - 23 Nov 2005 18:14 GMT

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

Signature

Kind regards,

Niek Otten

> Nick,
> The data is not sorted. The formula: =VLOOKUP(Chart!$B$1,EXP,53,FALSE)
[quoted text clipped - 26 lines]
>> > Thanks,
>> > Kem

Reply to this Message