Moving data from one Excel workboook to another, by matching common column fields as primary key in both workbooks

Question

I Have two excel workbooks with many sheets, one of the workbooks is to map the data (all the keys and data structures we use) and the other workbook is a data dictionary to get the DataFeed key which is what need to populate in the first workbook. both workbooks have a common attribute (SNL key) in all the sheets in the workbooks but they aren't on the same sheets. Can I set up a primary key (SNL key) to join the workbooks together and populate the empty DataFeed key in the first workbook. at the moment im going through it one by one and there has to be a more efficient way of doing this.

Answer 1

Try VLOOKUP. It's possible to use across files although I recommend copying one sheet into the other workbook so that you don't have to deal with the linkages that creates. The key is that the shared primary key has to be the first column in the lookup table.

Microsoft also recently introduced XLOOKUP which is a little more flexible and should allow you to do the same thing.

Seems like it's probably overkill here but you could also use Power Query with both tables as data sources and then create a merge using a SQL-like join (Data tab -> Get Data -> Combine Queries -> Merge).