Forum Discussion
How to combine and merge 2 spreadsheets in Excel?
One spreadsheet has names and phone numbers. The other spreadsheet has names and emails. How can I create a combined address book containing names, phone numbers, and emails from the 2 spreadsheet?
2 Replies
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.
https://exceloffthegrid.com/power-query-introduction/
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.
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.
