Vlookup with reference to multiple files from folder

[Surekha]

Hi Team,

I need a Vlookup formula, which will take values from all files saved in a particular folder.
E.g.

File 1:
Col1 Col2
123 Need to fetch Col2 value from files saved at folder
456
789

There are multiple files saved in folder such as File2, File3, File4, File 5 upto 10 files (Data format is same in all file).
So to get above value i need to open each file and applying lookup if not found will open second file and so on.. which is time consuming.

So need a formula which will fetch values from all files without opening a reference file.

Thanks in advance.
Surekha

 

[rahulshewale1]

HII @Surekha ,

max how many files in folder. ?


Regard
Rahul shewale

 

[Surekha]

Up to 10 files Rahul and format for all is same

 

[rahulshewale1]

hi @Surekha,

Which excel version are you using ?

If your are using 2010 or above version then you will get result using power query.


Regard
Rahul shewale

 

[Surekha]

Hi Rahul, thanks for your reply. But as said earlier in post i need a formula to fetch data from those files saved in folder. It will be possible using SQL query but I don't want to build connection over here. Also please note that file names may be different with different location so want to build dynamic formula.

 

[GraH - Guido]

Surekha, Still as rahulshewale1, suggests, it will be easier using power query, using "load files from folder" feature, which you can make dynamic, as long as there some sort of logic you can built with M-code. No need for "SQL query".
As a basic it is do-able using the UI...
As for a formulae solution, you could be more successful if you would upload some sample workbooks with the data structure in it and dummy data. + Explain how the dynamic naming is working.