How to Merge Multiple Email Lists in Google Sheets and Excel Without Losing Data: VLOOKUP, INDEX-MATCH, Power Query and Apps Script Methods
On this page
The Problem: Multiple Lists, One Truth
Businesses collect email addresses across many systems: CRM exports, marketing platform downloads, event registrations, e-commerce orders, webinar attendees, sales prospecting, support tickets and partner referrals. Each list has its own columns, formatting and level of completeness. Merging them into a single deduplicated list with the most complete information per contact is a common data task with several approaches depending on list size and complexity.
Method 1: VLOOKUP (Google Sheets and Excel)
VLOOKUP works when merging two lists and you want to pull columns from one list into the other based on a shared email column.
Setup
| Step | What to do |
|---|---|
| 1. Prepare both lists | Put List A in Sheet1 and List B in Sheet2. Ensure the email column is in the same position (typically Column A) in both sheets. Lowercase all emails first: in a helper column, use =LOWER(A2) and copy down |
| 2. Decide which list is primary | The primary list keeps all its rows. VLOOKUP adds data from the secondary list where emails match |
| 3. Write the VLOOKUP | In Sheet1, next to the primary list, add columns for each field you want from Sheet2 |
Formula
To pull the "Name" field from Sheet2 Column B where the email in Sheet1 Column A matches Sheet2 Column A:
=IFERROR(VLOOKUP(A2, Sheet2!$A:$B, 2, FALSE), "")
| Formula part | What it does |
|---|---|
A2 |
The email to look up (from the primary list) |
Sheet2!$A:$B |
The range to search: Column A (emails) and Column B (the field to return) |
2 |
Return the value from the 2nd column of the range (Column B) |
FALSE |
Exact match only |
IFERROR(..., "") |
If no match found, return empty instead of an error |
Limitations
| Limitation | Impact |
|---|---|
| Only searches left to right | The lookup column (email) must be the leftmost column in the range |
| Returns first match only | If Sheet2 has duplicate emails, only the first match is returned |
| Does not add unmatched rows | Emails in Sheet2 that are not in Sheet1 are silently ignored |
| Performance degrades with large lists | Noticeable slowdown above 10,000 rows |
Method 2: INDEX-MATCH (Google Sheets and Excel)
INDEX-MATCH is more flexible than VLOOKUP because the lookup column does not need to be leftmost, and it handles larger datasets more efficiently.
Formula
To pull the "Company" field from Sheet2 Column C where emails match:
=IFERROR(INDEX(Sheet2!$C:$C, MATCH(A2, Sheet2!$A:$A, 0)), "")
| Formula part | What it does |
|---|---|
MATCH(A2, Sheet2!$A:$A, 0) |
Finds the row number where Sheet2 Column A matches the email in A2 (0 = exact match) |
INDEX(Sheet2!$C:$C, ...) |
Returns the value from Column C at that row number |
IFERROR(..., "") |
Returns empty if no match |
When to use INDEX-MATCH instead of VLOOKUP
| Situation | Use |
|---|---|
| The email column is not the leftmost column | INDEX-MATCH (VLOOKUP cannot look left) |
| You are pulling many columns from a large range | INDEX-MATCH (each formula references only the columns it needs, not the entire range) |
| You need the last match instead of the first | INDEX-MATCH with MATCH in reverse (search mode -1 in XMATCH) |
| Performance matters (large lists) | INDEX-MATCH (generally faster than VLOOKUP on large ranges) |
Method 3: UNIQUE + FILTER (Google Sheets)
Google Sheets' UNIQUE and FILTER functions can combine and deduplicate two lists without manual formulas per row.
Step 1: Stack the lists
Combine both lists vertically using curly braces:
={Sheet1!A2:D; Sheet2!A2:D}
This creates one combined list with all rows from both sheets (assuming both have the same 4 columns: Email, Name, Company, Source).
Step 2: Extract unique emails
=UNIQUE({Sheet1!A2:A; Sheet2!A2:A})
This returns every unique email across both lists.
Step 3: Pull the most complete data per email
For each unique email, use FILTER to find matching rows and pick the best data:
=IFERROR(FILTER(Sheet1!B2:B, Sheet1!A2:A=E2), FILTER(Sheet2!B2:B, Sheet2!A2:A=E2))
This tries to get the Name from Sheet1 first; if not found, it falls back to Sheet2. Adjust the priority based on which source is more reliable.
Method 4: Power Query (Excel Desktop)
Power Query handles large merges (100,000+ rows) that formulas cannot, and provides a repeatable, refreshable process.
Steps
| Step | What to do |
|---|---|
| 1. Load both lists as tables | Select each list, Insert > Table (Ctrl+T). Name them (e.g., CRM_List, Marketing_List) |
| 2. Open Power Query | Data > Get Data > From Table/Range (for each table) |
| 3. Standardise email column | In each query: select email column > Transform > Lowercase > Trim |
| 4. Append queries | Home > Append Queries > Append as New. Select both queries. Result: one combined list |
| 5. Remove duplicates | Select email column > Home > Remove Duplicates. Keeps the first occurrence (order matters: arrange the more reliable source first before appending) |
| 6. Merge queries (for enrichment) | Instead of append, use Home > Merge Queries to join on email and pull columns from the secondary list into the primary. Choose Left Outer Join to keep all primary rows |
| 7. Load to worksheet | Home > Close & Load |
Advantages over formulas
| Advantage | Why it matters |
|---|---|
| Handles 100,000+ rows without slowdown | Formula-based approaches slow significantly above 10,000 rows |
| Repeatable / refreshable | When source data updates, refresh the query instead of rebuilding formulas |
| Transformation steps are documented | Each step is visible and editable in the query editor |
| Handles multiple sources | Append 5+ lists without nesting formulas |
| No formulas to maintain | Load once; results are static values (or refreshable connections) |
Method 5: Apps Script (Google Sheets)
For merging more than two lists or applying custom deduplication logic (e.g., keep the most recent record per email, merge non-empty fields across records), Apps Script provides full control.
Approach
| Step | What to do |
|---|---|
| 1. Read all sheets | Use SpreadsheetApp.getActiveSpreadsheet().getSheetByName("List1").getDataRange().getValues() for each list |
| 2. Build a map keyed by email | Create a JavaScript object (map) where each key is a lowercased email and the value is the merged record |
| 3. Merge logic | For each record, check if the email exists in the map. If yes, fill in empty fields from the new record (keeping existing non-empty values). If no, add the record |
| 4. Write results | Write the merged map to a new sheet |
When to use Apps Script
| Situation | Use Apps Script |
|---|---|
| Merging 3+ lists with different column structures | Map columns by header name, not position |
| Custom merge rules (most recent wins, longest value wins, specific source priority) | Code the logic explicitly |
| Recurring task (weekly list merge from multiple sources) | Set up a time-based trigger |
| Lists exceed 50,000 rows | Formulas become too slow; Apps Script processes in memory |
Handling Common Merge Issues
| Issue | Solution |
|---|---|
| Same email, different capitalisation (John@example.com vs john@example.com) | Lowercase all emails before merging: =LOWER() or Power Query Transform > Lowercase |
| Same email, different names (John Smith vs J. Smith vs Jonathan Smith) | Define a priority: longest name wins, or specific source is authoritative |
| Same person, different emails (john@company.com vs john.smith@company.com) | These are different records unless you have another matching field (phone, name + company). Merge on email alone keeps them separate |
| Empty vs. populated fields across sources | Merge logic: keep the first non-empty value per field per email |
| Conflicting data (different company names for same email) | Define source priority: CRM wins over marketing list, or most recent record wins |
| Leading/trailing spaces in emails | Trim before merging: =TRIM() or Power Query Transform > Trim |
When to Use Email Extractor Instead
Spreadsheet merging works for structured, column-aligned lists. But when your "lists" are not structured, upload them to Email Extractor instead:
| Situation | Why Email Extractor |
|---|---|
| Email addresses embedded in unstructured text (email threads, documents, notes) | Regex-based extraction pulls emails from any text; spreadsheet formulas cannot |
| Mixed file formats (some CSV, some DOCX, some PDF, some HTML) | Email Extractor handles 19 file types; spreadsheets require all data in rows and columns |
| You only need the email addresses (not the surrounding data) | Email Extractor extracts and deduplicates emails without needing column mapping or merge logic |
| Thousands of files | Upload a batch of files; Email Extractor processes them all and deduplicates across the batch |
| Data contains emails scattered across multiple columns or fields | Email Extractor scans every text field; no need to identify which column contains the email |
For structured list-to-list merging where you need to preserve and reconcile name, company, source and other fields alongside the email, use the spreadsheet methods above. For extracting and deduplicating email addresses from mixed-format, unstructured data, use Email Extractor.
