# Calc hangs on VLOOKUP, files larger then expected

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 close merge delete

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.

( 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.

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

OK , my english is not very well, thank you

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

Sort by » oldest newest most voted

hallo

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

more

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

( 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.

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

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.

more

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

( 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.

( 2017-09-23 14:44:12 +0200 )edit

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

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

( 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

( 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.

( 2017-09-24 11:32:40 +0200 )edit