0

In a workbook, I have a sheet with huge set of data around 7 Lacs. I have only two columns in that sheet. Lets say this workbook as WK-A

This huge data is used by me on daily basis for VLOOKUP. Now the issue is my VLOOKUP takes lot of time and also opening that WK-A takes lot of time.

There's no formula on WK-A, it just contains the plain text data.

Moreover, I am not doing VLOOKUP in single cell in second workbook. I have some 30000+ records which i am trying to get the data from first workbook. And this is taking lot of time.

I have tried VLOOKUP by kepping first workbook closed as well as open, but no much effect in time.

Can anyone suggest faster way to VLOOKUP these huge data???

Paresh J
  • 2,401
  • 3
  • 24
  • 31
  • 1
    Lac is equal to 100,000 – Paresh J Oct 24 '14 at 14:24
  • VLOOKUP is equivalent to a LEFT JOIN in a database. Perhaps consider moving your data to an RDBMS like Access to do this type of work. You could also consider using ADODB in VBA to query the two sheets as if they were DB tables to perform this, but it would probably be much more user-friendly to go the Access route. – JNevill Oct 24 '14 at 14:40
  • @JNevill: I guess you are right. Considering moving into Access is good option. Since my data is increasing every month and it would cross 10 lacs mark in next couple of month. But still any option i can do this in excel??? – Paresh J Oct 24 '14 at 14:50
  • 1
    I don't believe there are any good options in excel. You could do that whole ADODB/VBA thing, but that's essentially the same as the Access route. Doing anything else in VBA (like looping and searching) would probably be as slow as your vlookups if not as slow. – JNevill Oct 24 '14 at 14:52
  • But its a process change. But in a long run its a good option. Can you suggest how many rows can MS Access takes? I have office 2007. – Paresh J Oct 24 '14 at 15:10
  • If you have to do it in Excel, then don't use `VLOOKUP`. Use VBA instead. A dictionary or collection can deal with 500,000 rows easy. 700,000 rows won't make much more of a difference really. It might be a somewhat lengthy setup considering that the code should be made to work like a lookup, but the gains are far more than the initial investment. However, breaking into the 1 million row mark is not really that okay. As @JNevill said, moving this to a database is much better. Even 2 million rows is easy to query in a database. – WGS Oct 24 '14 at 15:18
  • Will dictionary able to handle this much data? I know search would be simpler but will it able to take this much number of records?? – Paresh J Oct 24 '14 at 15:25
  • @PareshJadhav, MS Access is limited only by the size of the HDD the DB file is saved on. You might also consider the free version of SQL server. It'll be lower-cost to set up(if you don't mind using an existing PC/Server to house it), but more complicated. – Mr. Mascaro Oct 24 '14 at 15:53
  • Cannot used SQL SERVER. But what do you think importing data into MS ACCESS will be time taking? I have to do this only once in a month. But still if its not efficient way then cant go for that option.. – Paresh J Oct 24 '14 at 16:07

2 Answers2

3

You can sort WK-A and use the double vlookup trick (assuming you want to know if there is no matching record in WK-A).

For details of the double lookup trick see http://fastexcel.wordpress.com/2012/03/29/vlookup-tricks-why-2-vlookups-are-better-than-1-vlookup/

But Excel will limit you to 1 million rows.

Charles Williams
  • 23,121
  • 5
  • 38
  • 38
0

Aside from the comments mentioned above (pull it into Access or utilize some type of RDBMS), you could utilize the INDEX and MATCH formulas native in Excel.

Using the INDEX-MATCH method is considerably faster as Excel will not have to reference as many records during its "lookup".

Learn more about this here and here.

Also see this SO post here.

Finally, consider sorting your records -- I am sure this alluded to in the references provided.

Community
  • 1
  • 1
JasonAizkalns
  • 20,243
  • 8
  • 57
  • 116