We're currently migrating from Ask to Discourse, read the details here

Ask Your Question

Calc hangs on VLOOKUP, files larger then expected [closed]

asked 2017-09-20 07:29:35 +0200

dimzev gravatar image

updated 2021-06-26 22:13:37 +0200

Alex Kemp gravatar image

I use spreadsheets with about 30000 rows and check with the Function VLOOKUP sheets of calculation with about 8000 rows and many times sticks- collapses (turns gray) and I have a big problem. the size of each file is from 10Μb to 18Mb, What must do? can help me ?C:\fakepath\LOupload.xls

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 2020-10-16 01:06:47.678295


When the sheet turns gray, that implies that the computer is busy (stressed-out). You didn't indicate if this is a new problem. If a new problem, that could be a result of a bad formula that is hogging CPU time. There could be other reasons too.

Steve R. gravatar imageSteve R. ( 2017-09-21 04:28:16 +0200 )edit

I reworded title and retagged question as it has better chance now to attract attention of someone who can actually provide solution.

Kruno gravatar imageKruno ( 2017-09-22 12:42:53 +0200 )edit

OK , my english is not very well, thank you

dimzev gravatar imagedimzev ( 2017-09-23 18:26:06 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2017-09-22 13:38:53 +0200

karolus gravatar image

updated 2017-09-22 13:39:26 +0200


Propably you can significantly speed up the recalculation of VLOOKUP if you sort the Search-Array by the First Column, and use VLOOKUP without 4th Parameter or 4th Parameter 1

edit flag offensive delete link more


i dont understand.. if serch is only First column how i take the result from second column ?

dimzev gravatar imagedimzev ( 2017-09-23 11:16:48 +0200 )edit

Think he wants you to sort your whole table by first column and then you use VLOOKUP without 4th paramater (or use '1' as 4th paramater). Make a backup copy of your spreadsheet, try this and see if you gain some speed.

Kruno gravatar imageKruno ( 2017-09-24 09:18:16 +0200 )edit

answered 2017-09-22 19:18:24 +0200

m.a.riosv gravatar image

There was an slowness issue with some functions like VLOOKUP searching in a range,if the option Menu/Tools/Options/LibreOffice calc/Calculation - Search criteria = and <> must apply to whole cells it's no marked.

edit flag offensive delete link more


in my case: >> Search criteria = and <> must apply to whole cells<< it is marked

dimzev gravatar imagedimzev ( 2017-09-23 11:13:41 +0200 )edit

Then please attach a sample file so some one can take a look for a better way to do it.

m.a.riosv gravatar imagem.a.riosv ( 2017-09-23 14:44:12 +0200 )edit

can you help me how can attach a sample fille please?

dimzev gravatar imagedimzev ( 2017-09-23 17:29:05 +0200 )edit

Edit your question and add the file to share.

m.a.riosv gravatar imagem.a.riosv ( 2017-09-24 01:30:49 +0200 )edit

i upload a simple fille to see my settings , i try to upload a large fille and i cant

dimzev gravatar imagedimzev ( 2017-09-24 09:16:32 +0200 )edit

No VLOOKUPs in your file, but maybe the issue is with STYLE function it is a volatile function, so with any change, any where in the file, they are calculated.

m.a.riosv gravatar imagem.a.riosv ( 2017-09-24 11:32:40 +0200 )edit

Question Tools

1 follower


Asked: 2017-09-20 07:29:35 +0200

Seen: 277 times

Last updated: Sep 24 '17