Hi,

What is the most optimal way (spreadsheet size vs speed) for Excel to access a lot of data?
I have 1.75 million records each containing: Postcode, District, County, Region.

For speed, I split the postcode data into 22 worksheets, each containing the postcode records starting with a particular letter, A,B,C,D, etc.
The formula below determines the first letter of the postcode, then looks up the corresponding data in column 2 (District) in the correct worksheet (A,B,C, etc.)

Then I put the formula into two additional columns to return the corresponding County and Region data. (Columns 3 & 4) and down to just 50 rows

The result is fast but the worksheet has grown from 2mb to 32mb. Which takes forever to load. No idea why.

So, I need help! What is the most optimal way (spreadsheet size vs speed) to for Excel to access a lot of data?
As I mentioned, I have 1.75 million records each containing: Postcode, District, County, Region

The formula I used which as quick but made the worksheet huge was:
IF(LEFT(E2,1)="A",VLOOKUP(E2,[Lookup.xlsx]A!$A$1:$E$24521,2,FALSE),
IF(LEFT(E2,1)="B",VLOOKUP(E2,Lookup.xlsx]B!$A$1:$E$169530,2,FALSE),
IF(LEFT(E2,1)="C",VLOOKUP(E2,Lookup.xlsx]C!$A$1:$E$148162,2,FALSE),
etc. down to
IF(LEFT(E2,1)="Z",VLOOKUP(E2,[Lookup.xlsx]Z!$A$1:$E$631,2,FALSE)