Ask Your Question

Vlookup not working on large data [closed]

asked 2012-08-22 17:50:49 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

I am trying to do vlookup on 50k, however every time Libreoffice calc gets in no-responding mode. Is there any other way to do vlookup in LibreOffice Calc?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-10-17 20:11:53.611612


Sorry, but let me tell that I not believe you! I have just tested the array of one million rows and VLOOKUP () worked very well. Maybe you have an error in a formula or in the data. Can you show your formula?

JohnSUN gravatar imageJohnSUN ( 2012-08-23 08:36:40 +0200 )edit

Thanks for your feed back John,Here I am trying to search this value =vlookup(A29,My_Data.A1:B157286,2,0), using (search criteion,array,index,sort order), finding value for 50k record from next sheet sheet which have 150k records, vlookup is working fine if i do on less record, i;e 2-3k, continue..

salimshaikh gravatar imagesalimshaikh ( 2012-08-23 09:58:55 +0200 )edit

However same formula not responding if applied over 3k or 10 k records, showing Fill row and adapt hieght row on bottow of spreadsheet.

salimshaikh gravatar imagesalimshaikh ( 2012-08-23 10:00:09 +0200 )edit

What is you have in cell A29? The text? Number? Date? What is you have in column A of the worksheet My_Data? Value types are the same? The entire length of the column? Try to change the formula to =vlookup(A29,$My_Data.$A$1:$B$157286,2,0)

JohnSUN gravatar imageJohnSUN ( 2012-08-23 12:17:58 +0200 )edit

I don't have issue with formula, its performance issue of Calc, i have tried the same formula, with same value in cell. i;e all value are in number format, its working on 100 records or more, but not able to apply same formula on large data,

salimshaikh gravatar imagesalimshaikh ( 2012-08-23 16:47:41 +0200 )edit

Showing Fill row and adapt hieght row on bottow of the spreadsheets and after few minutes file show not responding on title Currently using I5 core 2007 OS with 4 gb ram memory

salimshaikh gravatar imagesalimshaikh ( 2012-08-23 16:50:10 +0200 )edit

Please opne a bug report with a test document. We are always interested in test documents showing performance problems because we think that we have fixed the most obvious ones and now need some help of users to find more during import or during execution of calculations.

moggi gravatar imagemoggi ( 2012-08-25 18:51:23 +0200 )edit

Thanks for your feedback Moggi, i am not sure how to fetch bug report as my entire sheet get stuck while doing vlookup. Please let me know if you have any email address or other contac, so that i can share all screen shot and excell file were i actually doing vlookup.

salimshaikh gravatar imagesalimshaikh ( 2012-08-27 16:39:12 +0200 )edit

Any help on this?

salimshaikh gravatar imagesalimshaikh ( 2012-08-29 09:52:16 +0200 )edit

I am afraid that there is no help. You can try to fill in the search column in small portions - 1-2K. Then convert the result of the formula in the value and repeat. My experiments with alternative formulas searches lead to the same results - the computer for a long time to think.

JohnSUN gravatar imageJohnSUN ( 2012-08-29 13:54:16 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2012-08-22 18:02:59 +0200

tohuwawohu gravatar image

I think you should use Base instead of Calc for such a amount of data. Base is much more powerful handling a big amount of data. Here's a OOo forum thread how to import a Calc sheet in Base.

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2012-08-22 17:50:49 +0200

Seen: 3,275 times

Last updated: Aug 22 '12