Combine Excel Tables by Email Address
Question details
Combine records from two Excel tables using email addresses as the matching key.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Two tables have overlapping email addresses and different columns that need to be brought together.
- Observed behavior
- The desired result is one master list of email addresses with matching values from both tables.
Check that both tables have an email-address column and consistent email formatting. Review duplicate email addresses within each table, because standard lookup formulas return the first match.
Create a master email list and use INDEX and MATCH
Use this approach to combine records in Excel versions that support INDEX and MATCH, including older versions.
The example assumes two Excel tables named Table1 and Table2. Table1 contains Email Address and Phone columns, while Table2 contains Email Address and Department columns. Replace these names with your own table and column names.
On a new worksheet, enter Email Address in A1. Copy the email addresses from Table1 and Table2 into column A, placing the second list below the first.
Delete blank email cells, select the list in column A, and choose Data > Remove Duplicates. Confirm that the Email Address column is selected, then click OK.
Enter Phone in B1. In B2, enter =IFERROR(INDEX(Table1[Phone],MATCH($A2,Table1[Email Address],0)),"") and fill the formula down alongside the email list.
Enter Department in C1. In C2, enter =IFERROR(INDEX(Table2[Department],MATCH($A2,Table2[Email Address],0)),"") and fill the formula down. Repeat for any other columns you need.

Use UNIQUE and XLOOKUP to combine the tables
Use this approach in a version of Excel that supports VSTACK, FILTER, UNIQUE, and XLOOKUP.
Combine Email-Based Records in WPS Spreadsheets
Use a master email list and INDEX and MATCH formulas in WPS Spreadsheets to bring values from two worksheets together.
- 1. Open the workbook: In WPS Spreadsheets, open your Excel file. For this example, arrange the source data on sheets named Contacts1 and Contacts2, with email addresses in column A and the values to retrieve in column B.
- 2. Create the email list: Add a new worksheet named Master. Copy the email addresses from Contacts1 and Contacts2 into Master column A, place the second list below the first, and remove blank cells.
- 3. Remove duplicate emails: Select the email list on Master and use the Remove Duplicates command on the Data tab, then confirm the selected column.
- 4. Retrieve the matching values: In Master B2, enter =IFERROR(INDEX(Contacts1!$B$2:$B$1000,MATCH($A2,Contacts1!$A$2:$A$1000,0)),""). In C2, use the same formula with Contacts2 in place of Contacts1. Fill both formulas down and adjust the row limits to cover your data.
- 5. Save the combined workbook: Choose File > Save As and select an Excel workbook format such as XLSX.

Frequently Asked Questions
Why should I use email addresses instead of names?
Different people can share a name, so a name may match the wrong record. An email address is generally a more reliable key, provided it identifies the intended record in your data.
What happens if an email address appears in only one table?
Include addresses from both tables in the master list. The lookup for the table without a match returns a blank when you use IFERROR with INDEX and MATCH or the empty-string argument in XLOOKUP.
Why is a lookup returning a blank when another matching row has data?
INDEX and MATCH and the XLOOKUP examples return the first matching row, even if its value is blank. Review duplicate email addresses in the source table or use a lookup formula that also checks that the return column is not blank.
Will the master list update when I add new email addresses?
A manually copied and deduplicated list must be updated manually. The UNIQUE and VSTACK formula can update as source Excel tables grow, but you may still need to fill adjacent lookup formulas down to cover new rows.




