Data Import & Export

Combine Excel Tables by Email Address

Amos GikundaAmos Gikunda Nov 18, 2026 869 views

Question details

Combine records from two Excel tables using email addresses as the matching key.

Combine Excel Tables by Email Address
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.
Before you start

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.

Solution 1Recommended

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.

1
Build the master list

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.

2
Remove blanks and duplicates

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.

3
Retrieve the first table's values

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.

4
Retrieve the second table's values

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.

Create a master email list and use INDEX and MATCH
Check repeated email addresses: MATCH returns the first matching row, even if its return value is blank. Resolve duplicates or use a formula with an additional nonblank criterion if you need a later value.
Combine spreadsheet records

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. 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. 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. 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. 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. 5. Save the combined workbook: Choose File > Save As and select an Excel workbook format such as XLSX.
Match records by email address instead of potentially duplicate namesOpen and edit Microsoft Excel XLSX and XLS filesUse familiar spreadsheet formulas to retrieve source valuesCreate a distinct master list with duplicate-removal tools
QA img-9

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.