Hi,

I have a workbook I have built that has a data set showing 18 month's sales, and across this there are approx 250,000 lines of data. I have a need to insert columns into this spreadsheet, and am using vlookups off a master list to give the data in these columns that I require. Because of the large number of lines I estimate there are approx 1.5 million vlookups in this spreadsheet alone! Add to this I have approx 10 pivottables reading off this data sheet. The total file size is now 105Mb. I have been researching and cannot find a way to reduce the file size given I need to have these calculations in there.

I looked at Index Match however have read vlookup is approx 5% faster so don't think that will solve my problem. I am not overly familiar with array formulas however am not sure it I could implement these into this massive data extraction, or whether they are likely to reduce file size or increase it. I also read about alookup however am not familiar with its use. I believe with Excel 2007 this can be used?

Appreciate any help