Article
How to Verify a List with Multiple Email Columns (Step-by-Step)
By Unlimited Verifier Team ·

Summary
To verify a list with multiple email columns, unpivot the file so each address sits on its own row, remove duplicates, verify each unique address once, then join results back so every contact keeps one best address. Checking only the first column misses contacts whose secondary address still works.To verify a list with two or three email columns, unpivot it so each address sits on its own row, remove duplicates, verify each unique address once, then join the results back so every contact keeps one best address. The most common shortcut, checking only the first column, misses contacts: in the worked example below, it would drop 9.4% of the file's contacts from your sendable audience.
This guide covers the full job: spotting address columns, reshaping the file in Excel or Python, verifying and joining the results, and deciding which address to keep. If you want the end-to-end process first, read the bulk email list verification workflow and come back here for the multi-column step.
Quick answer
- Reshape before you upload. Put one address per row, tagged with a contact ID and a source column. Unlimited Verifier accepts CSV or Excel files up to 10MB and detects the email column automatically, so the reshape is the work you do first.
- Clean the cells first. Split cells that hold several addresses, and strip placeholders such as "n/a" and non-breaking spaces.
- Deduplicate before verifying. On Pro, the 25,000 monthly deep verifications are a finite quota, so repeated addresses waste checks you may need for spot checks.
- Use one rule set for conflicts. Promote a Valid secondary address when the primary is Risky or Invalid, but only after a consent check.
- Export results in time. Results are kept 24 hours on the Free plan and 30 days on Pro.
What counts as a list with multiple email columns?
A multi-column list is any contact export that stores more than one address per record. The most common headers are work_email, personal_email, email_2, alt_email, billing_email and notification_email.
These files usually come from a few predictable places:
- CRM exports that split work and personal addresses into separate fields.
- Ecommerce accounts, where the login address and the billing or receipt address often differ. This pattern is common in email verification for ecommerce and SaaS work.
- Event and webinar registrations, where attendees enter a second address "in case."
- Merged databases from acquisitions or platform migrations, where two systems each contributed a column.
Three layouts cause most of the trouble:
- Separate columns, one address per field. This is the easiest case.
- Stacked cells, where one field holds
a@firm.com; b@gmail.com. A verifier is likely to read this as one malformed address, so both good addresses can look invalid. - Confirmation columns, such as
confirm_email, that copy the primary address. Verifying them duplicates a check you have already made.
Bottom line: Before you verify anything, identify which columns hold addresses, which hold copies, and which hold placeholders.
Why does verifying only the first email column cost you contacts?
Secondary columns often hold the only address that still works for a contact. Teams commonly find this in B2C lists and mixed B2B2C lists, where a personal address outlasts a work address after a job change.
Ignoring those columns creates two problems:
- Lost reach. A contact whose primary address is dead but whose secondary is live drops out of your sendable audience without anyone noticing.
- Hidden risk. A dead secondary address still gets mailed if any process reads that column, such as a sync job, a manual export or a re-engagement segment. Each bounce adds to the reputation damage you are trying to avoid.
The second problem matters more than it looks. Mailbox providers judge senders partly on complaints and bounces. Google's email sender guidelines advise keeping the user-reported spam rate below 0.10% and warn that rates of 0.30% or higher can cause delivery problems. Mailing an unverified secondary address that belongs to a former employee or a closed account is one way that rate creeps up.
Bottom line: Verifying every address, not just the primary, keeps your reachable audience accurate and your complaint rate in check.
Which method should you use to verify multiple email columns?
The right method verifies each unique address once and maps the results back to every contact. The table compares the four common approaches.
| Method | How it works | Reach you keep | Main drawback | Verdict |
|---|---|---|---|---|
| A. Verify the primary column only | Check one column, ignore the rest | Lowest; misses contacts with a live secondary | Blind spot for every other column | Fine for a quick audit, not a full clean |
| B. Verify each column as its own upload | Upload work, personal and alt columns separately | High | The same address is checked several times and uses extra quota | Avoid |
| C. Reshape, deduplicate, verify unique addresses, join back | One check per address, results mapped to contacts | Highest | Needs a join step in a spreadsheet or script | Recommended for most lists |
| D. Verify and create a new contact per address | Every address becomes its own record in your ESP or CRM | High on paper | The same person can receive the same campaign twice | Avoid |
Method C takes more setup, but it spends the least quota for the most reach. It also gives you one status per address and one decision per person, which the other methods cannot do.
Bottom line: Reshape first, verify unique addresses once, then join results back to contacts. The rest of this article follows that method.
How do you verify a multi-column list, step by step?
Work through these seven steps in order. Each one prevents an error that would otherwise surface later.
- Audit the columns. List every field that holds an address. Mark confirmation columns and anything that is not really an email, such as names, phone numbers or notes. Why: an unmarked copy column gets verified and paid for twice.
- Normalize the text. Trim spaces, convert to lowercase and remove non-breaking spaces. Why: these characters are invisible in Excel but can cause a valid address to fail the syntax check.
- Split and strip. Break stacked cells into separate addresses, and remove placeholders such as
n/a,none,-,unknownand test entries. Why: placeholders are not mailboxes and inflate your invalid count. - Unpivot to one address per row. Keep a
contact_idand asourcecolumn (work, personal, alt). Why: the source column lets you apply different rules to different columns later. - Deduplicate the unique addresses. Build one list of distinct addresses for verification, and keep the full mapping file separately. Why: you verify an address once no matter how many contacts share it. See how to remove duplicate emails before verifying for the full method.
- Verify the unique list once. Upload it, or use the API for larger or recurring jobs. Export the results immediately. Why: results expire, so a late export can cost you the work.
- Join back and decide per contact. Match results to the mapping on the normalized email, then apply one rule set per contact, using the decision table below.
Bottom line: The mapping file makes the process reversible, so keep it until every decision has been written back.
How do you reshape, verify and join a multi-column list?
This tutorial uses Excel for the reshape and Unlimited Verifier for the check. A Python version follows for larger files.
Part A: Reshape in Excel
- Save your export as CSV with a header row. Open a copy in Excel and leave the original untouched.
- Clean the spaces. In a helper column, enter
=TRIM(SUBSTITUTE(A2,CHAR(160),""))and fill it down.SUBSTITUTEremoves the non-breaking space (character 160), whichTRIMalone does not remove. - Unpivot the email columns. Select the table, choose Data > From Table/Range to load it into Power Query, then select only the email columns and choose Transform > Unpivot Columns > Unpivot Only Selected Columns. You now have one row per address, with the original column name in an
Attributecolumn. - Split stacked cells. Use Split Column by Delimiter with a semicolon, then unpivot again if needed. Filter out any row without an
@. - Remove duplicates. Copy the email column to a new sheet, choose Data > Remove Duplicates, and save the result as
to_verify.csv.
Part B: Verify
- Sign up if you do not have an account, then upload and verify the list. Send only the email column, so the result does not depend on how a tool handles extra fields. The upload accepts CSV or Excel files up to 10MB, so split a larger file first. For format details, see what file formats email verifiers accept and how to upload a CSV to an email verifier.
- Export the results as CSV or Excel the same day. Results are kept for 24 hours on Free and 30 days on Pro.
- Sort the buckets. Valid addresses are safe to send to. Invalid addresses should be suppressed. Risky addresses need more care: a catch-all domain accepts any address, so a result there does not confirm a mailbox. For how each bucket should be handled, read valid vs risky vs invalid email results explained and what to do with unknown emails after verification.
Part C: Join back
- Match on email. In your mapping sheet, put the normalized address in column C and add a status column with
=XLOOKUP(C2, verified!A:A, verified!B:B, "not checked"). Replace the sheet and column references with the ones in your export. Use VLOOKUP in Excel versions without XLOOKUP. - Write one decision per contact. Apply the rules in the decision table below, then write a
send_tocolumn and a status flag for each address.
Python version (for large files)
import pandas as pd
df = pd.read_csv("contacts.csv", dtype=str)
cols = ["work_email", "personal_email", "alt_email"] # match your headers
long = df.melt(id_vars=["contact_id"], value_vars=cols,
var_name="source", value_name="raw")
long["raw"] = long["raw"].fillna("").str.replace("\u00a0", " ")
long["email"] = long["raw"].str.split(r"[;,\s]+") # split stacked cells
long = long.explode("email")
long["email"] = long["email"].str.strip().str.lower()
long = long[long["email"].str.contains("@", na=False)]
# Unique addresses go to the verifier; the full map stays with you
long["email"].drop_duplicates().to_frame("email").to_csv("to_verify.csv", index=False)
long.to_csv("address_map.csv", index=False)
# After verification: check the column names in your export first
results = pd.read_csv("verified.csv", dtype=str)
merged = long.merge(results, on="email", how="left", validate="many_to_one")
print(len(long), len(merged)) # the two counts should match
Reading with dtype=str stops IDs and phone-like values from being converted to numbers, a common source of silent corruption. The pandas documentation covers the functions used here.
Bottom line: Reshape, verify the unique list, and join on the normalized email. Most spreadsheet errors happen at the join, so check that the row count after the merge matches your mapping.
What does a worked example look like for a 12,000-contact file?
The file below is hypothetical. The starting counts and result percentages are illustrative, but the plan limits are the product's real ones, and the arithmetic is shown in full.
The file: 12,000 contacts with work_email, personal_email and alt_email.
Counting addresses:
- Populated cells: 11,400 work + 7,200 personal + 1,100 alt = 19,700
- Placeholders removed: 19,700 − 140 = 19,560
- Stacked cells holding a second address (300 cells): 19,560 + 300 = 19,860 address rows
- Repeats removed (the same address in two columns, shared inboxes, and addresses shared across contacts): 19,860 − 1,860 = 18,000 unique addresses
Fitting the plan:
- Pro: 18,000 is under the 25,000-address-per-list cap, so it runs as one list. If you run deep verification on every address, that uses 18,000 of the 25,000 monthly deep verifications, or 72%, and leaves 7,000 for spot checks. At $29 a month, the job costs $1.61 per 1,000 addresses on monthly billing, or $1.39 on the $25 annual rate (our arithmetic on the list prices). For how to compare that with other email verification pricing models, read the cost guidance in our buyer's checklist.
- Free: Each list is capped at 150 addresses, so 18,000 addresses need 120 lists. The Free plan allows 25 lists a month, or 3,750 addresses. The job would take five months.
Results (hypothetical):
- Valid: 15,120 (84%)
- Risky: 1,980 (11%)
- Invalid: 900 (5%)
Joining back to 12,000 contacts:
- 10,560 (88%) have at least one Valid address.
- 9,430 have a Valid primary address.
- 1,130 (9.4% of contacts) are reachable only through a Valid secondary. A primary-only check would have missed them.
- 1,020 (8.5%) have no Valid address but at least one Risky one. Hold these back.
- 420 (3.5%) have no Valid or Risky address. Suppress their email, but keep the record for other channels.
The 1,130 recovered contacts are the headline. At $29 for the month, the verification costs about $0.026 per recovered contact. The gain is in reach, not in the verifier's bill.
Bottom line: Deduplicating first cut 19,860 rows to 18,000 checks, and the secondary column recovered 9.4% of the file's contacts.
Which address should you keep when statuses conflict?
Use one rule set for every contact, and write both statuses back to your system rather than overwriting the record. The table covers the common pairings.
| Primary | Secondary | Action | Why |
|---|---|---|---|
| Valid | Valid | Mail primary; keep secondary as backup | Two live routes; the primary is the default |
| Valid | Risky or Invalid | Mail primary; do not mail secondary | Keeps the weak address out of sends |
| Risky | Valid | Promote secondary after a consent check | The mailbox is live, but the purpose must fit the consent given |
| Invalid | Valid | Promote secondary after a consent check | Same as above |
| Risky | Risky | Hold; re-check in a later run | Neither can be confirmed as deliverable yet |
| Invalid or blank | Invalid or blank | Suppress email; keep the contact for other channels | Mailing dead addresses adds bounces |
If a large share of your file comes back Invalid, the problem is usually upstream, in how the data was collected. See what to do if 30 percent of your list is invalid, and use how to segment a list by verification status to build sending groups from these results.
Consent comes before promotion. A verified address proves the mailbox exists, not that the person agreed to marketing. A personal address collected only for account recovery or billing may not carry marketing permission.
The rules differ by jurisdiction. US CAN-SPAM is an opt-out regime for commercial email: it requires a working opt-out, accurate sender information and honoured unsubscribe requests, but not prior consent. The FTC's CAN-SPAM compliance guide sets out the requirements. In the UK and EU, PECR and GDPR generally require consent for electronic marketing to individuals, with a soft opt-in exception for existing customers in some cases. The ICO publishes guidance on the UK position. Check the terms under which each column was collected before you promote an address.
Bottom line: Verification tells you whether an address works. Your rule set and consent records tell you whether you may use it.
How do you keep the contact record intact and compliant?
Store one contact record with a status for each address, and avoid creating duplicate contacts. Fields such as work_email_status, personal_email_status and send_to keep the history visible to whoever opens the record next.
Shared addresses need extra care. A file like the one above will often contain an info@ or sales@ inbox attached to dozens of contacts. Sending one campaign to all of them delivers repeated copies to a single mailbox. Deduplicate at send time as well as at verification time, and use the role-address flag to decide who receives what. Treat verification as one control alongside documented consent and suppression, which is the approach in email verification compliance and hygiene.
Gmail addresses need a specific note. Gmail ignores dots in the username and treats anything after a plus sign as the same inbox, so john.smith@gmail.com and johnsmith+news@gmail.com reach one mailbox. Verify each address exactly as entered, because a verifier checks the string you give it. Apply this normalization only when you deduplicate sends, and only for Gmail and googlemail.com addresses.
Bottom line: Keep one contact, a status per address, and a send rule. A complete history makes the next audit much faster.
Can you automate multi-column verification?
Yes, for recurring or large jobs. The email verification API and automation approach suits pipelines that run weekly or sync data continuously.
With Unlimited Verifier's email verification API, you can check single addresses or send bulk batches of up to 10,000 emails per request. The 18,000-address file above needs two requests: one of 10,000 and one of 8,000. API access is included on Pro, which covers 100,000 API emails a month. It is not available on Free.
The API suits recurring jobs, but the cheapest fix is to stop bad secondary addresses at entry. Adding real-time email validation to any form that collects a second address catches typos and placeholders before they reach your database. For a maintenance schedule that covers recurring checks, see email list maintenance best practices.
Bottom line: Use the API for recurring jobs and checks at entry, and the upload for one-off cleans.
What mistakes should you avoid with multiple email columns?
- Verifying only the primary column. This drops contacts with a live secondary and leaves dead secondaries in the file.
- Verifying the confirmation column. It copies the primary address, so the check spends quota on a duplicate.
- Deduplicating after the upload. Repeats use quota before you have removed them. Deduplicate first.
- Leaving stacked cells unsplit. A cell holding
a@x.com; b@y.comis likely to be read as one malformed address, which can make both good addresses look invalid. Our guide to cleaning a messy CSV of emails before upload covers this in detail. - Joining on row numbers or names. Sorting and filtering shift rows. Join on the normalized email.
- Promoting secondaries without a consent check. A live address is not automatically a permitted one.
- Overwriting the primary. When you promote a secondary, keep the original in its own field so the history survives.
- Missing the export window. Results are kept 24 hours on Free and 30 days on Pro. Export the day you verify.
- Deleting rather than suppressing. Removing an invalid address from the file can cause it to be re-imported by the next sync. Suppress it as well. The reasoning is covered in should I delete or suppress invalid emails.
Bottom line: Most errors happen before verification, during the reshape, or after it, at the join. Check both.
How should you choose a verifier for multi-column work?
Choose a verifier by how it handles your workflow, not only by its accuracy claims. Our guide to how to choose an email verifier covers the wider comparison, and the best email verification tools overview shows how the options differ. For multi-column work, these questions matter most.
| Question | Why it matters | What to check |
|---|---|---|
| Does the upload accept a single-column file cleanly? | Determines how much preparation you do | Documented file types, size limits and column detection |
| What status categories does it return? | Drives your decision rules | Clear Valid, Risky and Invalid buckets, with a reason for Risky results |
| How long are results kept? | Affects your export routine | Retention period stated in the plan details |
| Is there an API for recurring jobs? | Matters for weekly or real-time work | Batch size per request and which plans include access |
| How do standard and deep checks count against limits? | Determines your real cost per address | Whether standard checks are unlimited and what the deep quota covers |
Unlimited Verifier's documented plans match this workflow in several ways:
- Unlimited standard verification on every plan, including Free. Re-running the unique list after fixes carries no per-email fee.
- Three buckets with catch-all detection, plus flags for disposable and role-based addresses.
- Export as CSV, Excel or JSON, so the join can run in the tool you already use.
- An API with batches of up to 10,000 emails per request, with 100,000 API emails a month on Pro.
- An open-source verification engine on GitHub, so you can read how the checks are coded before you trust them.
Be clear about the limits as well. Unlimited Verifier has no native ESP, CRM or Zapier integrations, so the join happens in your spreadsheet or script, as shown above. Uploads are capped at 10MB, and the Pro plan limits each list to 25,000 emails. The free email checker is useful for spot-checking a single high-value address, such as a key account's secondary that came back Risky. For catch-all results, see the catch-all email verification guide.
Bottom line: The right verifier is the one whose upload, results and limits match how you will actually run the join.
How we compared (and limitations)
This article is based on public documentation: SMTP mailbox standards from the IETF's RFC 5321, Google's sender guidelines, the FTC's CAN-SPAM guidance, the ICO's guidance on electronic marketing, and the documented behaviour of pandas and Excel. We have not run controlled tests of any verifier, and we make no claims about how competing products handle multi-column files.
Unlimited Verifier's figures come from its published plans: 25 lists a month of up to 150 emails each on Free, with 750 deep verifications and no API access; unlimited lists of up to 25,000 emails each, 25,000 deep verifications and 100,000 API emails a month on Pro at $29 a month ($25 billed annually); results kept 24 hours on Free and 30 days on Pro; and API batches of up to 10,000 emails. The company states 99.5% accuracy for deep verification. That is Unlimited Verifier's own figure, not an independent benchmark.
The worked example is hypothetical. Its counts and percentages are illustrative, and the cost-per-1,000 figures are our arithmetic on the plan prices. Verification confirms that a mailbox exists, but not consent, so the rules in this article still apply. Confirm current limits and retention on the pricing page before you plan a large job.
Bottom line: Use the method and the arithmetic with your own counts, and confirm your plan limits before a large run.
Worked example: first-column-only loss
| Contact | work_email | personal_email | First column only | Multi-column result |
|---|---|---|---|---|
| 101 | a@x.com (Invalid) | b@y.com (Valid) | Dropped | Keep b@y.com |
| 102 | c@x.com (Valid) | blank | Keep | Keep c@x.com |
| 103 | d@x.com (Invalid) | e@y.com (Risky) | Dropped | Keep e@y.com if consent allows |
Checking only the first column drops contacts 101 and 103 from the sendable audience, even though 101 has a working address. In the article's example, this approach removes 9.4% of contacts.
Frequently asked questions
Can I verify multiple email columns without reshaping the file?
Yes, but you need a separate verification pass for each column, then a join step to combine results. Reshaping first is usually simpler because it lets you deduplicate addresses that appear in more than one column and verify each one only once.
How do I split cells that contain several email addresses?
Use a text-to-columns or split function on the delimiter, usually a semicolon or comma, then unpivot the resulting columns. Trim spaces and remove non-breaking characters before splitting so each address is clean.
Which email should I keep when a contact has two addresses?
Keep the address with the strongest verification result, such as Valid over Risky or Invalid. If the primary address is weaker, promote a Valid secondary only after confirming you have consent to email it.
Should I verify confirmation columns like confirm_email?
Usually not. Confirmation columns often copy the primary address, so verifying them repeats a check you already made. Compare them to the primary column and skip any duplicates.
Why do stacked cells with two addresses fail verification?
Most verifiers read a cell with a semicolon-separated pair as one malformed address, so both valid addresses can appear invalid. Splitting the cell into separate rows fixes this.
How do I join verification results back to the original contacts?
Keep a contact ID on every row during reshaping, then match each verified address back to its contact ID with a lookup or join. Each contact should end with one chosen address and a note of its source column.
Does deduplicating before verification save credits?
Yes. Repeated addresses across columns or contacts would otherwise be checked more than once, using quota that is limited on paid plans. Deduplicate first so each unique address is verified once.
How long should I keep verification results?
Export results before the retention window closes. Retention periods differ by plan, so check the current platform documentation and save a copy to your own storage when the job finishes.
Cut Bounces and Protect Your Sender Reputation With Unlimited Email Verification
Every invalid address you send to hurts your sender reputation and your inbox placement. Verifying your list before each send means fewer bounces, healthier deliverability, and a predictable cost. With Unlimited Verifier, standard verification is unlimited on every plan, with no per-email fees.
Unlimited Verifier checks syntax, domain, MX records and the SMTP mailbox. It also detects catch-all domains and flags disposable and role-based addresses. Export a CSV from your email platform or CRM, upload and verify the list, then re-import the clean results. You can also automate checks with the email verification API.
| Free | Pro | |
|---|---|---|
| Price | $0 | $29/month ($25/month billed annually) |
| Lists | 25 a month, up to 150 emails each | Unlimited, up to 25,000 emails each |
| Deep verifications | 750 a month | 25,000 a month |
| API access | No | Yes (100,000 emails a month) |
| Result retention | 24 hours | 30 days |
Why teams choose Unlimited Verifier
- Unlimited standard verification on all plans, including Free, with no per-email fees.
- Catch-all detection so you know which domains accept every address.
- Clear Valid / Risky / Invalid results, exportable as CSV, Excel or JSON.
- An open-source verification engine you can inspect on GitHub.
Unlimited Verifier states 99.5% accuracy for deep verification.
Create a free account and verify your next list before you hit send.
Want to test a single address first? Try the free email checker.