Trying to VLookup from separate workbook based on a value in a column.

[IainLewis]

Hi,

I don't even know if this is possible, but I'm hoping to simplify a step to avoid having to run several exports from our employee database, and then copy them into one document.
Essentially we have 15 different "company numbers" and the way our payroll software works, I cannot run one export from all, I need to run each separately and copy them into one documents just now to do the next task, which isn't the end of the world, however If I can set it up so I run the export form payroll directly to a set folder, with a set name, is it possible to pull from several different files, based on data in a set column in the original.

As an example, If I know employee 123 is in company 2000, can I set it up so that if I enter the company number and employee number it will look in the file named "2000" and pull back the necessary info on employee 123?

As it stands at the moment, I can get everything I need, but the only way I could get it to work was to have an "export" tab in my main workbook, which I currently have to copy all information from each of the 15 exports and combine them into one worksheet.

Like I said, I don't even know if this is possible, but any help would be very much appreciated.

Many Thanks In advance

Iain

[alansidman]

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file.

[IainLewis]

Hi,

My apologies, I couldn't upload the original documents as there is a lot of sensitive information, so I have mocked up some simple workbooks.
In theory What I would like to be able to do would be to have all of these saved in one place and if I go into "Main Workbook" and type in the employee number and payroll number it would then pull across their name and department from the correct workbook.

E.g if the employee is in payroll no 1000 it would pull the name and department from the workbook titled "1000".

Many Thanks

Iain

[JeteMc]

I feel that what will be needed is to produce a single lookup table, which could be done using Get & Transform (Power Query).
The video tutorial linked below is produced by an instructor at Highline College and will hopefully be helpful.
https://www.youtube.com/watch?v=ldoQws7Zbx8

[Gregb11]

Well, here's an option. (since you're on MS365 you can use XLOOKUP, but earlier versions could use other options)

On the main workbook, you have the list of EE#'s and their Payroll Number, under "Name" you could enter:

=XLOOKUP(A2,INDIRECT("["&$B2&".xlsx]Sheet1!A$2:A$5"),INDIRECT("["&$B2&".xlsx]Sheet1!$B$2:$C$5"),"Not found",0)

This would bring back both the name and the department.
You could copy this formula down.

the A2:a5 and b2:c5 would need to be the longest range where compare the lengths of employees on all the payroll numbers and use the one the the highest for your range.