VLOOKUP or INDEX/MATCH or similar - across multiple workbooks

[CraigG]

Hello

I have one master workbook with many columns of data, in column A is a unique reference number (it's not duplicated).

The data is then in several (at least 20) separate workbooks, again the unique reference number is not duplicated either within the workbook or across the different workbooks - it only appears once in any of the workbooks (so there is duplication or risk of it being in many workbooks).

The columns / rows, data, etc, are all in the same format, same columns, etc across all the many workbooks.
At the end of each row users will enter data (using a drop down menu using a list in data validation).

In the master workbook I need a way of using VLOOKUP or INDEX/MATCH to find the unique reference number in the different workbooks and bring in the data (which is in the same column in all of the workbooks).

For example,
MasterWorkbook.xlsx - contains all rows and unique reference number (in column A)
Workbook1.xlsx - contains some of the unique reference numbers (column A) and additional information in column Z
Workbook2.xlsx - contains some of the unique reference numbers (column A) and additional information in column Z

In MasterWorkbook.xlsx I need to find the unique reference number (column A) in Workbook1.xlsx or Workbook2.xlsx then bring into column Z the data from column Z from the many workbooks.

I know I could use IFERROR, VLOOKUP or INDEX/MATCH, but as I have many workbooks to check is there a better way I could this?
(I don't want to do a manual copy and paste as I have many workbooks and several thousand rows).

Thanks for your help.

 

[GraH]

Power Query comes to mind.
Append all input workbooks, and merge with master data.
Google it... Since your data seems well structured, it is easy and 100% do-able with the UI. Meaning some mouse clicks... and you're done. And later you simply click refresh to redo the process whenever there is an update.

 

[CraigG]

Power Query comes to mind.
Append all input workbooks, and merge with master data.
Google it... Since your data seems well structured, it is easy and 100% do-able with the UI. Meaning some mouse clicks... and you're done. And later you simply click refresh to redo the process whenever there is an update.

Thanks - I don't know why I didn't think of that!