Faster way to VLOOKUP huge set of data

Hello Guys,

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.

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


Regards,
Paresh J

I am not sure but i think Index and Match function is best

7 lacs = what - 7 000? 70 000?

vlookup should not take that much time to run, what do you mean by

Now the issue is my VLOOKUP takes lot of time and also opening that WK-A takes lot of time.

What does your vlookup formula look like?
If you END HOME, where does your cursor end up? If it is not in the last cell of your 2nd row, you have some extra, unnecessary formatting or data in that sheet that you can probably delete

Hi FDibbins,

I mean 7 lacs rows of data. I don't have any formatting to my data in my workbook. Also, when i do END HOME, i get to the last row of the 2nd column.

My formula looks like :

Please Login or Register  to view this content.

Regards,
Paresh J

Do you have more than 700,000 rows of data?

Yes, More than 700,000 data. My VLOOKUP operation is taking time. Any solution to this???

Vlookup is faster than Index/ Match. I'm wondering if you have volatile formulas that are being updated when the workbook is being opened or when each vlookup formula returns a value. Since you are searching data from another workbook, then vlookup has to open that workbook (if not already open).

Hi abousetta,

I don't have any formulas in that workbook. And even i am not opening the workbook. I have the full path of other workbook in the VLOOKUP formula. Check my formula in post #4.


Regards,
Paresh J

For vlookup to work the workbook has to be open. If not, Excel will open it. Try running the same formula with the workbook open already.

I have already done the same. But no major change in time.

I'd have to see your files because vlookup is pretty fast. Try one last thing. Turn calculations to manual >> Double click on the cell with this formula >> hit Enter and see what happens. If it's faster then there are other formulas in your current workbook that are recalculating and slowing down the whole process.

For vlookup to work the workbook has to be open

Not really, vlookup works just fine on closed workbooks

Hi abousetta,

Sorry, but i can't share the file. It is very confidential.
But 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 time.

And my main concern is data in the first workbook is getting increased every month. In next couple of months it will cross 1000000 records(i.e 10 Lacs). What has to be done then?

Any solution???


Regards,
Paresh J

Originally Posted by FDibbins

Not really, vlookup works just fine on closed workbooks

I stand corrected.

@FDibbins,

Absolutely, It works with closed workbooks too. I have checked in both ways. But not much help.

Originally Posted by pareshj

Hi abousetta,

Sorry, but i can't share the file. It is very confidential.
But 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 time.

And my main concern is data in the first workbook is getting increased every month. In next couple of months it will cross 1000000 records(i.e 10 Lacs). What has to be done then?

Any solution???


Regards,
Paresh J

I really can't tell without seeing the workbooks, but it seems that you need to either split the workbook into multiple workbooks or invest in more robust hardware.

EDIT: You also might want to install Office as 64-bit installation so that you can tap into more than 2GB of memory (if that's the limiting factor)

----

double post

Excuse me.

----------------

Hi,

an attempt: could you sort your data in ascending order?

Can you keep your data sorted by column A in:

'D:\Data\ABC.xlsx'!A2:B715263

I do not know if it's convenient.

Cheers

@abousetta:

Memory is one limiting factor and I agree to you completely. But i have to manage in the same resource somehow.

Hi again

the idea is to use a different strategy by using a binary search for vlookups

Please Login or Register  to view this content.

Formula needs sorted data

Cheers

Edit: one comma was missing in <>lookup

@canapone:

Doing two LOOKUP operations will take time. Dont you think??

Sorting the data is difficult lookup column is text. IT contains numbers with length of 8 digits and those who are not are prefixed with zero's. Hence sorting these data is not possible.

Hi,

thanks for providing feedback: if you can't conveniently sort your data, this strategy does not make sense.

Doing two LOOKUP operations will take time. Dont you think??

No, I don't.

VLOOKUP(...FALSE) does a linear search

VLOOKUP(...TRUE) does a binary search

For a formula doing two binary searches on sorted data is much faster than doing one linear search - i.e. vlookup(...false)- with any data larger than 100 rows.

I'm not sure about potential problems with external references or closed workbooks using LOOKUP.

I would need to make some tests on some real sample: easy I'm totally (or partially) wrong.

Cheers

So guys........ Whats the solution....... what should I do??

I've noticed for large numbers of VLOOKUPs, (over a million at a time), referencing a closed workbook can take a LONG time if the file being referenced has a lot of data and/or is stored on a network/remote hard drive. If you open the workbook and calculate, it should not take nearly as long.

@Speshul,

Both files are on my local disk and in same folder. I have checked the both ways by closed as well as open workbook but does not make much of difference.

Maybe it is just a network bottleneck then, nevermind! I'm not sure.

I don't understand why sorting is difficult because the data is text. What difference does that make?

@romperstomper,

Is it possible to sort data like this:

00023451
00589874
00000015

Yes. It should sort correctly either as text or as numbers since they're formatted to have the same number of characters.

But if i sort it as text, it will not give me desired output and same cannot be converted to numbers as it would un necessary increase steps from converting text to numbers and then sort the numbers and then convert to text again.

Originally Posted by pareshj

But if i sort it as text, it will not give me desired output

In what sense?

Will it give output like this:

00000015
00001258
00025892

@Loganeb,

Yes, this is what i am looking for now.

Originally Posted by pareshj

Will it give output like this:

00000015
00001258
00025892

Yes - you could of course try it...

VLOOKUP takes less than one second on 1.000.000 records
But if you have a lot of formulas in your workbook it can be slow, especially with array-formulas
I have attached a test workbook
When you open it you should first click on button: Generate 1.000.000 records
This will take 1 minute
Then you click on button: Remove Duplicates
This will take 15 seconds
Then you click on button: Sort Data
This will take 2 seconds
Now the file will be 35 Mb big
If you select a value in cell: F3 it will do the Vlookups (in column: L) within a second
In cell G3 I put a UDF that does a Binary Search which is quicker
Hope it will work............