Can we use multiple Vlook Formulae

[aksan7]

I have file which is huge (32MB). Due to size and large data's in the file i had to split the files into 5 files so that other people can work on the files.


Now i need the new data's inputted in the 5 different split files to the main file.


What alternative formulaes would be helpful to have these fixed.


Lookup value is on Column B, Result are in Column C and D.


Let me know if you need any other information.

 

[prasaddn]

Hi,


It is better you can use vba coding to consolidate data from more than one file to one main file.


if you need to use only vlookup formulae then use,


for Col C

1. vlookup() referring to each file in col E, F, G, H & I

2. in col C use formulae ==IFERROR(E16,IFERROR(F16,IFERROR(G16,IFERROR(H16,IFERROR(I16,"Not Found"))))), change 16 to your appropriate row.

This will fetch the value if present in any of the file or result "Not Found" if not found.


Do the similar stuff for Col D data.


Regards,

Prasad

PS: I know this is tedious method

 

[NARAYANK991]

Hi aksan1 ,


I feel that the huge size of your file may have something to do with the way your data has been organized.


Another reason for such a big file may be that you are retaining data which is old , and may not need to be retained.


Regarding organization of data , the data which needs to be together , and which has significance when read together , needs to be in the same worksheet ; since formulae can refer to cells across workbooks , there should not be any necessity to consolidate all data in one worksheet. When databases are being maintained , normalization is a very important step in proper database design. The techniques of normalization can be used even when you are planning your Excel worksheets.


Any comments ?


Narayan

 

[Hui]

Askan7


Firstly, Welcome to the Chandoo.org Forums


Have you tried saving the file as an Excel Binary file (*.xlb or *.xlsb)?

Excel Binary files are highly compressed and you have no loss of functionaility or speed