Function Problems

How to Combine Multiple Email Addresses into One Cell by ID in Excel

Ayan Masood Sep 28, 2026 869 views

Question details

The user needs to consolidate multiple email addresses associated with a single ID into one cell to facilitate data retrieval via a VLOOKUP function in another workbook.

How to Combine Multiple Email Addresses into One Cell by ID in Excel

Product: Microsoft Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Preparing a dataset where multiple email entries exist per ID, requiring them to be grouped and combined into a single comma-separated cell for lookup compatibility.
Observed behavior: Emails for the same ID are currently spread across multiple rows. The goal is to generate a unique list of IDs, each with a single row containing all related emails merged together.

Before you start

Ensure your version of Excel or WPS Spreadsheet supports dynamic array formulas, as functions like UNIQUE and FILTER are required for this solution to work efficiently.

Solution 1Recommended

Use UNIQUE and ARRAYTOTEXT Formulas to Combine Data

This method extracts a unique list of IDs and merges all corresponding email addresses into a single cell formatted as text.

By nesting FILTER inside TOROW and wrapping it with ARRAYTOTEXT, you can dynamically extract all matching emails for an ID and output them as a single comma-separated string.

Extract Unique IDs

Select a blank cell (e.g., E2) and enter the formula =UNIQUE(A2:A8) to generate a distinct list of ID numbers from your original dataset.

Filter and Combine Emails

In the adjacent cell (e.g., F2), enter the formula =ARRAYTOTEXT(TOROW(FILTER(B2:B8,A2:A8=E2)),0). This filters the emails in column B that match the ID in E2, turns them into a row, and converts the array to text.

Apply Formula to All IDs

Select cell F2 and drag the fill handle down to apply this formula to all the unique IDs generated in column E.

Use UNIQUE and ARRAYTOTEXT Formulas to Combine Data

Best Practices for Data Management: While keeping individual values in separate cells is often best practice for database management, merging them into one cell is highly effective and sometimes necessary for VLOOKUP retrieval.

Solution 2

Alternative: Use TEXTJOIN with FILTER

If you prefer a custom delimiter (like a semicolon) or are using a version that supports TEXTJOIN but not ARRAYTOTEXT, this is a great alternative.

Advanced Formulas in WPS Spreadsheet

Combine Cells and Process Data Seamlessly in WPS Office

WPS Spreadsheet fully supports advanced dynamic array formulas like UNIQUE, FILTER, and TEXTJOIN, allowing you to easily consolidate and manage large datasets for lookups.

1. Open Your Dataset: Launch WPS Spreadsheet and open the workbook containing your ID and email data.
2. Extract Unique Records: Use the UNIQUE function in a new column to isolate distinct ID numbers.
3. Merge Associated Data: Apply the TEXTJOIN and FILTER functions together to merge all email addresses corresponding to each unique ID into a single cell.
4. Save and Reference: Save your organized file in .xlsx format, which can now be seamlessly referenced by VLOOKUP in your other workbooks.

Fully compatible with Microsoft Excel formulas and .xlsx file formats.Built-in support for advanced dynamic array functions to easily combine and filter data by ID.Lightweight application with a fast, responsive, and familiar interface.Free to use for everyday data processing, merging, and complex analysis.

microsoft office alternative - wps office

Frequently Asked Questions

Can I use a different delimiter instead of a comma when combining emails?

Yes, by using the TEXTJOIN function instead of ARRAYTOTEXT, you can specify any delimiter. For example, =TEXTJOIN(" ; ", TRUE, FILTER(B2:B8, A2:A8=E2)) uses a semicolon with spaces instead of a default comma.

Why does the FILTER function return a #CALC! error?

The #CALC! error usually occurs if the FILTER function does not find any matching data for the specified ID. You can prevent this by adding a default value in the FILTER function, such as: =FILTER(B2:B8, A2:A8=E2, "No Email Found").

Can I combine emails by ID in older versions of Excel without dynamic arrays?

In older versions that lack UNIQUE or FILTER, you cannot use these dynamic array solutions directly. You will need to use a combination of IF and concatenation in helper columns, use Power Query to group by ID and merge rows, or write a VBA macro to loop through and combine the data.

Excel & Data Problems