Vlookup

[ibrahimaa]

I am using Vlookup in one of xls files that users have it on their desktop to search for variable data located on our server that I update it monthly. The user file is about 1MB while the server file is about 6MB. Because of Vlookup, the user file expanded almost to 6.5MB to contain the server file.

According to our company internal policy, this size of the user file (6.5MB) is not recommended for different reasons. Is there any other formula or method that can provide the same function in different than Vlookup’s technique? I appreciate your support. Thank you. :)

[VISHALS]

i think after you have already done the vlookup you copy paste same as values. or just keep the formula on only the first row and paste the others as values. sometimes i do it like that because me also we are not allowed to keep file with larger size.

[JoePublic]

Unless you copied the VLOOKUP formula into thousands of cells, it should not have that effect. If you do have thousands of them, then the chances are that the file size is down to the number of formulas as much as what the formulas are (in terms of length).

[ibrahimaa]

=IF($B6=7,VLOOKUP($F$5,'\\Server name & file bath\[File name.xlsx]July-Data'!$A$9:$R$7915,3,FALSE),

IF($C6=8,VLOOKUP($F$5,'\\Server name & file bath\[File name.xlsx]Aug-Data '!$A$9:$R$7915,5,FALSE),

IF($C6=9,VLOOKUP($F$5,'\\ Server name & file bath\[File name.xlsx]Sep-Data'!$A$9:$R$7915,7,FALSE), "Data is not Available yet")
) )

The server file contains 8 tabs; from May until Dec where each month data is in a separate tab.
So, if the user entered July in $B6 in the user file, the Vlookup will search in July tab of the server file.

I do not have any formulas in the server file. Thank you.

[Ken Puls]

I'm not totally clear on this. Which file is 6.5MB?

Honestly, from what I see here I'd probably be leaning to setting up a database and using a SQL query to pull out the info you're looking for.

[ibrahimaa]

Can you please help? I would appreciate your help. Thank you.