Vlookup and file size increase - relationship

[rangarjn]

I have a file with 10 columns and 220 rows. I am trying to do a vlookup of values from another excel with 94,000 rows and 20 columns. My initial size before vlookup is few Kbs in xlsx format. When I added Vlookup formula, file size increased to 15 MB and it cannot be sent in email to others. Why is the file size increasing so much for Vlookup usage. Does Excel store the value of vlookup data in some hidden area in my current excel?

I need to do 2 Vlookups from the big file with 94,000 rows. I need to check 1 column and if there is no matching data, need to do a second lookup for another column value. Will doing 2 Vlookups cause a problem with file size. Is there any other optimal way to do this?

 

[Hui]

This sounds interesting?

What version of Excel are you using ?

 

[NARAYANK991]

Hi ,

Can you post the formula you are using , and can you confirm whether all the VLOOKUP formulae are similar in nature to the posted one ?

Narayan

 

[rangarjn]

I am using Microsoft Office 2007 Professional Plus.

1 correction, in the other file, vlookup is for 40th column. I checked and formula contains $A$2:$A:$94404 and not entire column like A:A.

Other point, I want to mention is, size of the file reduced back to 30KB, when I used "Break Links" and removed the reference to the other workbook.

 

[NARAYANK991]

Hi ,

Try using an INDEX + MATCH combination , instead of a VLOOKUP.

Narayan

 

[Hui]

As Narayan asked could you post the actual VLookup formula?

If it was =Vlookup(Value, [Workbook]Worksheet!$A$2:$A:$94404, 40) as there is only 1 column it would give an error

Indirect is also very good for using across Workbooks

 

[otewguy]

By default, excel saves external link values. Disable that by going into File, Options, Advanced, and then untick the Save External Link Values in Formula section