Vlookup Error Message - Excel 2003

[Kim]

I have two vlookup functions in one spreadsheet linking data from two other
spreadsheets. I have 23K rows and I received an error of "Excel cannot
complete this task with available resources. Choose less data or close other
applications." My IT department is saying that I should query data in a table
(access) rather than storing data of 20K + rows using the Vlookup in excel.
Shouldn't excel be able to handle 600K rows of data using the Vlookup
function?

 

[Don Guillett]

When you say spreadsheets, do you mean files or workbooks? If so, Excel does
not like large external databases.

 

[p45cal]

I have two vlookup functions in one spreadsheet linking data from tw
other
spreadsheets. I have 23K rows and I received an error of "Excel cannot
complete this task with available resources. Choose less data or clos
other
applications." My IT department is saying that I should query data in
table
(access) rather than storing data of 20K + rows using the Vlookup i
excel.
Shouldn't excel be able to handle 600K rows of data using the Vlookup
function?

I doubt Excel 2003 would be able to handle 600k rows since there's
limit per sheet of about 65k rows, however I imagine Excel would easil
be able to handle 20k+ rows for a VLookUp, the question is, can th
machine it's running on do so

 

[Kim]

files

When you say spreadsheets, do you mean files or workbooks? If so, Excel does
not like large external databases.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[Jim Thomlinson]

Check out this link on memory and calculations...

http://www.decisionmodels.com/index.htm

If you really mean 600k rows then you are defintly in trouble here. If you
meant 60k rows from an external spreadsheet then you are still in a bit of
hot water but you might be able to tweak things to make them work.

As opposed to a vlookup which has pile of overhead you could look at using
an external database query to the sheets involved (a much smaller footprint).

 

[Kim]

That's exactly what I questioned and my IT Dept says that the machine Dell
Optiplex 755 with 2 GB of RAM, intel core 2 vpro processor, should be able to
handle the amount of data I am dealing with, but they said that excel Vlookup
should not be the tool I use to do my data comparison (I don't buy it).

I am trying to determine if the error I am receiving is my machine or the
Vlookup function.

 

[Kim]

I used 600K rows as an example of what I thought excel could handle using the
Vlookup function. My current situation is only dealing with 23K rows of data.
I will check out this link you supplied, thanks.

 

[Francis]

if you have 600K rows of data, your IT is correct to suggest using a database
application. However, for 20K rows ++ of data, Excel can handle that.

Try closing other applications that are running at the same time
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another

 

[Jim Thomlinson]

Check out the link I sent you. It is not a machine issue. XL has memory
limits that you will reach long before you reach your machines limit. Your
problem is that vlookup has huge overhead especially if you are looking at
external files. Relational databases handle this kind of thing MUCH better.
--
HTH...

Jim Thomlinson

That's exactly what I questioned and my IT Dept says that the machine Dell
Optiplex 755 with 2 GB of RAM, intel core 2 vpro processor, should be able to
handle the amount of data I am dealing with, but they said that excel Vlookup
should not be the tool I use to do my data comparison (I don't buy it).

I am trying to determine if the error I am receiving is my machine or the
Vlookup function.

 

[Jim Thomlinson]

Closing other apps will not help. Check out memory at the following link...

http://www.decisionmodels.com/index.htm