Article content and detailed guides remain in English. The selected language applies to controls and quick instructions.
Back to articles List management
VLOOKUP, XLOOKUP and INDEX-MATCH for Email List Management in Excel and Google Sheets By Email Extractor Published October 10, 2026 5 min read
VLOOKUP XLOOKUP Excel Google Sheets email list management
On this page Email data lives in multiple spreadsheets: CRM exports, event sign-ups, purchased lists, website form submissions and partner data. Lookup formulas are how you merge, compare and enrich these lists without manual matching:
Task
Without lookups
With lookups
Find duplicates across two lists
Manual side-by-side comparison
Formula flags matches in seconds
Add company names to an email list
Copy-paste one at a time
Formula pulls from reference sheet
Check which emails are already in CRM
Visual scanning
Formula returns "Already in CRM" or blank
Merge data from two exports
Manual matching by email
Formula joins data on email key
Identify unsubscribes from a send list
Compare lists manually
Formula marks unsubscribed contacts
Enrich with custom fields from another source
Manual data entry
Formula pulls matching fields
The Three Lookup Functions
VLOOKUP (vertical lookup)
Aspect
Details
Syntax
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Available in
Excel (all versions); Google Sheets
How it works
Searches the first column of a range for a value and returns a value from a specified column
Limitation
Can only search the leftmost column; returns one column at a time
Best for
Simple lookups where the email is in the first column of your reference data
XLOOKUP (modern replacement)
Aspect
Details
Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Available in
Excel 365 / Excel 2021+; Google Sheets
How it works
Searches any column for a value and returns a value from any other column
Advantage over VLOOKUP
No column index number needed; searches any direction; custom not-found message; handles errors natively
Best for
All lookups in modern Excel; cleaner and more flexible than VLOOKUP
INDEX-MATCH (classic flexible lookup)
Aspect
Details
Syntax
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
Available in
Excel (all versions); Google Sheets
How it works
MATCH finds the row position; INDEX returns the value from that position in another column
Advantage over VLOOKUP
Searches any column; faster on large datasets; more flexible
Best for
Older Excel versions; very large datasets; advanced multi-criteria lookups
Task 1: Find duplicates between two lists
Scenario: You have a new email list (Sheet1) and want to check which emails already exist in your master list (Sheet2).
Function
Formula (in Sheet1, checking against Sheet2)
Result
VLOOKUP
=IF(ISNA(VLOOKUP(A2,Sheet2!A:A,1,FALSE)),"New","Duplicate")
"New" or "Duplicate"
XLOOKUP
=XLOOKUP(A2,Sheet2!A:A,Sheet2!A:A,"New","Duplicate")
Displays email if found; "New" if not
INDEX-MATCH
=IF(ISNA(MATCH(A2,Sheet2!A:A,0)),"New","Duplicate")
"New" or "Duplicate"
COUNTIF (simpler)
=IF(COUNTIF(Sheet2!A:A,A2)>0,"Duplicate","New")
"New" or "Duplicate"
Task 2: Pull company name from a reference list
Scenario: You have emails in column A and need company names from a reference sheet where email is in column A and company is in column B.
Function
Formula
Notes
VLOOKUP
=VLOOKUP(A2,Reference!A:B,2,FALSE)
Returns company from column B of reference
XLOOKUP
=XLOOKUP(A2,Reference!A:A,Reference!B:B,"Not found")
Cleaner; shows "Not found" for missing
INDEX-MATCH
=INDEX(Reference!B:B,MATCH(A2,Reference!A:A,0))
Works in all Excel versions
Task 3: Merge data from two sources
Scenario: You have emails with names in one export, and the same emails with company names in another export. You want all data in one sheet.
Step
Formula (assuming email in A2, looking up Reference sheet)
Purpose
Pull first name
=XLOOKUP(A2,Reference!A:A,Reference!B:B,"")
Get first name from reference
Pull last name
=XLOOKUP(A2,Reference!A:A,Reference!C:C,"")
Get last name from reference
Pull company
=XLOOKUP(A2,Reference!A:A,Reference!D:D,"")
Get company from reference
Pull multiple columns at once (XLOOKUP)
=XLOOKUP(A2,Reference!A:A,Reference!B:D,"")
Returns all three columns at once (Excel 365)
Task 4: Check unsubscribes against a send list
Scenario: You have a send list and an unsubscribe list. Mark anyone who has unsubscribed.
Function
Formula
Result
XLOOKUP
=XLOOKUP(A2,Unsubs!A:A,"Unsubscribed","Active")
"Active" or "Unsubscribed"
COUNTIF
=IF(COUNTIF(Unsubs!A:A,A2)>0,"Unsubscribed","Active")
Same result; simpler
Task 5: Case-insensitive email matching
Email addresses are case-insensitive, but spreadsheet lookups are case-sensitive by default. This causes missed matches:
Approach
Formula
Notes
Pre-clean with LOWER
=VLOOKUP(LOWER(A2),LOWER(Reference!A:B),2,FALSE)
Array formula in older Excel; works in Google Sheets
XLOOKUP (case-insensitive by default)
=XLOOKUP(A2,Reference!A:A,Reference!B:B)
XLOOKUP is case-insensitive by default
Best practice
Clean both lists first: in a helper column, =LOWER(TRIM(A2))
Eliminates case and whitespace issues
Task 6: Fuzzy matching for typos
When email lists have typos (jsmith@gmial.com vs jsmith@gmail.com ), exact lookups fail. Strategies:
Approach
Formula or method
Best for
Domain extraction + name comparison
=LEFT(A2,FIND("@",A2)-1) for local part; =MID(A2,FIND("@",A2)+1,LEN(A2)) for domain
Separate matching by email parts
EDIT distance (Google Sheets add-on)
Fuzzy Match add-on; Fuzzymatch() custom function
Catching typos like gmial/gamil
Exact domain match + manual review
=IF(MID(A2,FIND("@",A2)+1,100)=MID(B2,FIND("@",B2)+1,100),"Domain match","Different domain")
Narrowing manual review to same-domain entries
Tip
Why it helps
When to use
Use INDEX-MATCH instead of VLOOKUP on 100K+ rows
INDEX-MATCH is faster; VLOOKUP scans entire columns
Large datasets in older Excel versions
Use XLOOKUP in Excel 365
Optimised for performance; handles large ranges
Excel 365 with large datasets
Sort reference data and use approximate match
Sorted data with approximate match is much faster
Very large reference tables (1M+ rows)
Convert ranges to Excel Tables
Structured references; auto-expansion; better performance
Any dataset that grows over time
Use helper columns with LOWER(TRIM())
Pre-clean once instead of in every formula
When lookup formulas are slow due to nested functions
Copy formula results and paste as values
Removes formula recalculation overhead
After lookups are complete; before sharing
Use Power Query (Excel) or Apps Script (Google)
Handles millions of rows; more efficient than formulas
Enterprise-scale email list management
Before Using Lookups: Clean Your Data
Lookup formulas only work well with clean data. Before running lookups, extract and deduplicate your email addresses. Upload your raw data files (CSV, XLSX, TXT, PDF) to Email Extractor to extract email addresses from mixed-format files and remove duplicates. This gives you a clean, deduplicated email column that lookup formulas can match reliably, rather than dealing with formatting inconsistencies, hidden characters and duplicate entries that cause VLOOKUP and XLOOKUP to return errors or miss matches.
