Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 406
  • Last Modified:

***500 points** Excel-Vlookup

We are using excel 2007 (we had the same issues with 2003).

When we move our excel document to a network share, any of the document that have vlookup no longer work or if they do work they are incredibly slow.

When all the files are on a local harddrive the response is very fast..

For example: We have a shreadsheet that the CEO users for pricing.. When he does a global "find/replace" on the spreadsheet with all the files locally, it takes about 5 seconds, (1300 replacments).. When I move that same files (all the files are in one folder) to a network drive the same find/replace takes about 5 minutes if it completes. Sometimes it crashes excel...

Has anyone else experienced this issue ?

Does any-one have an idea why this is occuring ? Does Vlookup not work across a LAN ?

This is a high priority issue as I need to get the file onto a file server but cannot upset our CEO :)

Any help or suggestions are greatly welcomed...
0
paadmin
Asked:
paadmin
  • 3
  • 3
1 Solution
 
Rory ArchibaldCommented:
VLOOKUPs can be quite inefficient, particularly if they reference large ranges of data. Have you tried using INDEX/MATCH combinations instead? What are the find/replaces that are being done?
Regards,
Rory
0
 
paadminAuthor Commented:
Hey Rorya,

I am not a excel person but our CEO lives and dies by it in everything he does.. I have never used the index/match combiniations but I will begin to look into these..

The find/replace is usually a date value but can be a price value also..

So he me.. do a find for 3.3.07 and replace it with 3.12.07

Thanks for the response,
Liam
0
 
Rory ArchibaldCommented:
If he does that a lot, it may be easier to put the required date in a cell and reference that in the formulas instead. Then you can just change the date in one cell.
If the workbooks use vlookups to return successive columns from a table, then INDEX/MATCH will be much more efficient because you can store the row number from the match in a column and then refer to that in several INDEX formulas rather than looking up the same value several times. For example, instead of:
=VLOOKUP(A1,Sheet2!A1:E1000,2,false)
=VLOOKUP(A1,Sheet2!A1:E1000,3,false)
=VLOOKUP(A1,Sheet2!A1:E1000,4,false)
=VLOOKUP(A1,Sheet2!A1:E1000,5,false)

you would use MATCH in a blank cell (say B1) to retrieve the row number:
=MATCH(A1,Sheet2!A1:A1000,0)
then refer to that cell in the INDEX formulas:
=INDEX(Sheet2!B1:B1000,B1)
=INDEX(Sheet2!C1:C1000,B1)
=INDEX(Sheet2!D1:D1000,B1)
=INDEX(Sheet2!E1:E1000,B1)

HTH
Rory
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
paadminAuthor Commented:
I will try that with him once he's available. Would that improve the slowness across the network ?
0
 
Rory ArchibaldCommented:
It should certainly help. He might also try setting calculation to manual, then doing the find/replace and then setting calculation back to automatic to see if that improves things.
0
 
dr_shivanCommented:
paadmin,

The reason for the lag is because you're transmitting data thru the Local Area Network and back. Imagine the whole world is sharing files on the server.. its like having a waiter to cater for more than 1 users.

My advise is to save a local copy to work on, and then save a backup in the server. Thus youhave 3 copies of the file which is redundant but saves computing time
0
 
paadminAuthor Commented:
Dr Shivan,

Thanks for your response, I am trying to avoid having a copy locally due to that fact that we have 60 g.b of excel spreadsheets and this would cause a lot of confusion if we have multiple copies in several locations.

Thanks
Liam
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now