Excel 2013 File having multiple formulas taking too much time to Open - Pls Help!!

[vs.suresh]

Dear Gurus,

My main file is having around 30 columns and all the 30 columns are filled with data fetched from four different files using vlookup and other formulas.

File 1 Contains 8K line items with 20 columns
File 2 Contains 8K line items with 40 columns
File 3 Contains 12K line items with 20 columns
File 4 Contains 3K line items with 40 columns

When I open the main file it is taking around 15 to 20 minutes to open the file. It shows (Calculating: (4 PROCESSOR(S)): 0%)....

The following formulas are used in the main file...
=MID(E24,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},E24&"0123456789")),4)
=INDEX($C$2:$C$11,MATCH(F2&" "&G2,INDEX($A$2:$A$11&" "&$B$2:$B$11,0),0))
=VLOOKUP(B2,File1.xlsx!$A:$B,2,FALSE)
=IFERROR(VLOOKUP(B2,File2!D:E,2,FALSE)," ")
=IFERROR(VLOOKUP(NUMBERVALUE(N2),File3.xls!$B:$T,19,FALSE),"")
=NUMBERVALUE(Z2)+NUMBERVALUE(AA2)
=IFERROR(VLOOKUP(NUMBERVALUE(N2),File4.xls!$B:$E,4,FALSE),"")
=TRIM(A2)
=IF(L2=M2,M2,L2)

Please help me to resolve this problem?

Regards,
Suresh

[JohnTopley]

Try changing VLOOKUPs to use fixed ranges rather than whole columns


=VLOOKUP(B2,File1.xlsx!$A2:$B20000,2,FALSE)

[jason.b75]

Have you tried opening files 1,2,3 and 4 before opening the main file?

[vs.suresh]

Yes, I have tried both... But still it is slow...

[Jonmo1]

I second the suggestion of limiting ranges to the actual used area of the sheet.

In addition, I would recommend using a helper column for this formula

=INDEX($C$2:$C$11,MATCH(F2&" "&G2,INDEX($A$2:$A$11&" "&$B$2:$B$11,0),0))

Insert a column between B and C, so th Indexed column moves to D
IN C2 and filled down put
=A2&" "&B2

Then use
=INDEX($D$2:$D$11,MATCH(G2&" "&H2,$C$2:$C$11,0))



I don't know if this is a Big factor, but you can reduce the number of function calls by changing
=NUMBERVALUE(Z2)+NUMBERVALUE(AA2)
to just
=Z2+AA2

Also in your vlookups, instead of using NUMBERVALUE(N2), just use N2+0



And finally, can the data in your lookup tables be sorted in Ascending order ?
Example
=IFERROR(VLOOKUP(B2,File2!D:E,2,FALSE)," ")

Can File2 be sorted in Ascending order by column D ?

If YES, then try
=IF(LOOKUP(B2,File2!D:D)=B2,LOOKUP(B2,File2!D:E)," ")

YES, 2 LOOKUPs are much (and I mean MUCH) faster than 1 VLOOKUP with FALSE as the lookup type.

[vs.suresh]

Thanks Jonmo... Now the time drastically reduced....

[Jonmo1]

You're welcome.