Nov 02 2021 08:22 PM
Nov 02 2021 11:10 PM
The most robust way would be to use Power Query (PQ). Provided you're not using Excel for the Mac, that is. Connect to both sheets and merge the two data sets based on Name. The advantages are:
1) No need to open either of the workbooks;
2) You will not change anything in the original workbooks;
3) No formulae required.
Now PQ can be a bit overwhelming if you have never used it before. Many extra buttons to press and terms to learn. The link below is a good starting point, I believe.
In the beginning of Chapter 8, you'll learn about connecting to data in separate workbooks.
Chapter 15, in particular, describes how you can perform lookup functions with the Merge tool in PQ.
If PQ is not for you, you can always use the VLOOKUP or XLOOKUP (in newer Excel versions) function. Google for either of these and you'll find plenty of resources describing its use.
Nov 02 2021 11:29 PM
I normally prefer to follow the dynamic array route rather than PQ and pivot tables. When it comes to ETL tasks merging data from multiple workbooks, I am right with you though. Power Query is the purpose written tool for the job and does it superbly.