Results 1 to 6 of 6

Thread: Vlookup

  1. #1

    Cool Vlookup

    Register for a FREE account, and/
    or Log in to avoid these ads!

    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. :)

  2. #2
    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.

  3. #3
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Excel Version
    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).
    This website wants to know your momentum - | Deny | | Allow |

  4. #4
    =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.

  5. #5
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Nanaimo, BC, Canada
    Blog Entries
    Excel Version
    Excel Office 365 Insider
    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.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book Master Your Data for Excel and Power BI, or our new Power Query Recipe cards!

    Main Site: -||- Blog: -||- Forums:
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts