Look here, particularly at the Lookup link:

http://www.decisionmodels.com/optspeed.htm

In article ,
(John) wrote:

My department currently uses lookups contained in 6 workbooks that
essentially roll-up into a seventh summary workbook. Obviously, this
causes crashes and resource depletion.

Does anyone have any general ideas to improve upon this? I'm thinking
about building and access db and linking to the tables to replace the
lookups...but, in the meantime is there any quick fix? (I'm not too
familiar with Excel, but am very familiar with Access). Maybe
replacing the lookups with match/index or something?

I'll continue researching the Excel groups to get a better
understanding of the lookup problems.

TIA,
John