Vlookup multiple file help

Copper Contributor

I need help extracting a number from multiple excel files. This number is not on the same cell in each file however always the cell next to it is the same name i.e "subtotal". It is always on the same vertical column i.e "I". Please help! Since I have more than 900 files to extract one number from them and place it in another file.   

3 Replies

Hi, @Sherouk Saleh.

Can all the files be in the same folder?

Do the files have more than one sheet?

Is the cell with the value that you need always in a sheet named the same?

I am thinking we could use Power Query to extract what you need. The most recent versions of Excel have Power Query included. 

Otherwise, we may need to use VBA. Let me know.

 

Hello, @Celia_Alves 

Yes, I placed it in the same folder. 

Yes the files have more than one sheet, but in all files, the sheet has the same name "Invoice"

The cell differs from one file to another, but in all files, the number I want to extract has a cell next to it with a name "Ins-Takaful". 

I need to collect this number and place it in a separate sheet showing "
Invoice number and this number"

I'll attach two files for you to check.

Thanks in advance

@Sherouk Saleh, please see file attached and follow instructions on sheet "Instructions."

 

I hope this helps.