Article content and detailed guides remain in English. The selected language applies to controls and quick instructions.
Back to articles List management
Spreadsheet Conditional Logic for Email List Management: IF, IFS, SWITCH, AND, OR Formulas for Segmentation, Scoring and Data Quality By Email Extractor Published October 10, 2026 4 min read
spreadsheet conditional logic email segmentation lead scoring data quality
On this page Why Conditional Logic Matters for Email Lists
Most email list management decisions follow "if this, then that" rules: if a contact has not opened an email in 90 days, flag them as inactive; if a domain is a free email provider and the contact has a VP title, flag for review; if a contact matches three scoring criteria, label them as high priority. Spreadsheet conditional formulas automate these decisions across thousands of rows.
Core Conditional Functions
IF (basic conditional)
Formula
What it does
Email list use
=IF(A2="","Missing","Present")Checks if email field is empty
Flag rows missing email addresses
=IF(LEN(B2)-LEN(SUBSTITUTE(B2,"@",""))=1,"Valid count","Check")Checks for exactly one @ symbol
Basic email syntax validation
=IF(TODAY()-C2>90,"Inactive","Active")Checks if date is older than 90 days
Flag contacts inactive for 90+ days
=IF(D2>3,"Engaged","Low engagement")Checks if open count exceeds threshold
Segment by engagement level
IFS (multiple conditions without nesting)
IFS evaluates multiple conditions in order and returns the result for the first TRUE condition. Cleaner than nested IF for three or more outcomes:
Formula
What it does
Email list use
=IFS(D2>=10,"Hot",D2>=5,"Warm",D2>=1,"Cool",TRUE,"Cold")Categorises by engagement count
Lead temperature scoring from open/click data
=IFS(E2>100000,"Enterprise",E2>10000,"Mid-market",E2>1000,"SMB",TRUE,"Micro")Categorises by company size
Company size segmentation for targeted campaigns
=IFS(F2="CEO","C-suite",F2="VP","Executive",F2="Director","Senior",F2="Manager","Mid",TRUE,"Other")Categorises by title
Seniority segmentation
SWITCH (exact value matching)
SWITCH matches a value against a list of exact matches. Cleaner than IF/IFS when comparing one cell to several specific values:
Formula
What it does
Email list use
=SWITCH(G2,"gmail.com","Personal","yahoo.com","Personal","outlook.com","Personal","Business")Labels domain type
Separate business from personal emails
=SWITCH(H2,"US","CAN-SPAM","CA","CASL","UK","UK GDPR","EU","GDPR","Other")Maps country to regulation
Compliance tagging by jurisdiction
=SWITCH(I2,"Subscribed","Active","Unsubscribed","Remove","Bounced","Remove","Complained","Suppress","Unknown")Maps status to action
Automated list action assignment
AND / OR (combining conditions)
Formula
What it does
Email list use
=IF(AND(D2>=5,E2>10000),"Priority","Standard")Both conditions must be TRUE
High-engagement + large company = priority
=IF(OR(F2="CEO",F2="CTO",F2="CFO"),"C-suite","Other")Any condition TRUE is enough
Flag C-suite contacts for executive campaigns
=IF(AND(D2>=3,NOT(J2="Unsubscribed")),"Send","Skip")Engaged AND not unsubscribed
Campaign send list qualification
=IF(AND(OR(F2="VP",F2="Director"),E2>5000),"Target","Non-target")Senior title AND mid-market+
ABM target identification
Email domain extraction and classification
Formula
What it does
=RIGHT(A2,LEN(A2)-FIND("@",A2))Extracts domain from email address
=IF(ISNUMBER(MATCH(RIGHT(A2,LEN(A2)-FIND("@",A2)),FreeList,0)),"Personal","Business")Checks domain against a named range of free email providers
=IF(COUNTIF(A:A,A2)>1,"Duplicate","Unique")Flags duplicate email addresses
=IF(AND(LEN(A2)>5,ISNUMBER(FIND("@",A2)),ISNUMBER(FIND(".",A2,FIND("@",A2)))),"Passes basic check","Fails basic check")Basic email format validation
Engagement scoring
Formula
What it does
=IF(D2>=10,"Highly engaged",IF(D2>=5,"Engaged",IF(D2>=1,"Somewhat engaged","Not engaged")))Four-tier engagement classification from open count
=D2*1+K2*3+L2*5Weighted engagement score (opens x1, clicks x3, replies x5)
=IF(AND(D2>=5,K2>=2,M2>=1),"MQL","Not qualified")Marketing qualified lead based on opens, clicks and form fills
=IFS(TODAY()-N2<=7,"Active this week",TODAY()-N2<=30,"Active this month",TODAY()-N2<=90,"Active this quarter",TRUE,"Dormant")Recency-based engagement tiering
Data quality checks
Formula
What it does
=IF(OR(A2="",B2="",C2=""),"Incomplete","Complete")Flags rows missing required fields (email, name, company)
=IF(AND(LEN(A2)>0,LEN(B2)>0,LEN(C2)>0,D2>0),"Good","Needs attention")Multi-field quality check
=IF(LEFT(LOWER(A2),4)="info","Role-based",IF(LEFT(LOWER(A2),5)="admin","Role-based",IF(LEFT(LOWER(A2),5)="sales","Role-based","Personal")))Detects role-based email addresses
=IF(ISNUMBER(FIND("+",LEFT(A2,FIND("@",A2)))),"Has plus alias","Standard")Detects plus-addressed emails (user+tag@domain)
Compliance and suppression
Formula
What it does
=IF(ISNUMBER(MATCH(A2,SuppressionList,0)),"Suppress","Clear")Checks email against suppression list (named range)
=IF(AND(O2="Opted in",NOT(ISNUMBER(MATCH(A2,SuppressionList,0)))),"Can email","Do not email")Combined opt-in and suppression check
=IF(AND(H2="EU",P2<>"Explicit"),"GDPR: need consent","Clear")Flags EU contacts without explicit consent
=IF(AND(H2="CA",Q2<>"Express"),"CASL: need consent","Clear")Flags Canadian contacts without express consent
List segmentation
Formula
What it does
=IF(AND(E2>10000,F2="VP",D2>=5),"Tier 1",IF(AND(E2>5000,D2>=3),"Tier 2",IF(D2>=1,"Tier 3","Tier 4")))Four-tier prospect segmentation
=IF(AND(R2="Customer",D2>=5),"Upsell candidate",IF(AND(R2="Trial",D2>=3),"Conversion candidate",IF(R2="Churned","Win-back candidate","Nurture")))Lifecycle stage segmentation
=TEXTJOIN(", ",TRUE,IF(D2>=5,"Engaged",""),IF(E2>10000,"Enterprise",""),IF(F2="CEO","Executive",""))Multi-tag assignment (array formula in some spreadsheets)
Practical Workflows
Lead scoring model in a spreadsheet
Build a scoring model by assigning points to each attribute, then summing for a total score:
Column
Formula
Points
Title score
=IFS(F2="CEO",10,F2="VP",8,F2="Director",6,F2="Manager",4,TRUE,1)1-10
Company score
=IFS(E2>100000,10,E2>10000,7,E2>1000,4,TRUE,1)1-10
Engagement score
=IFS(D2>=10,10,D2>=5,7,D2>=1,4,TRUE,0)0-10
Total score
=SUM(TitleScore,CompanyScore,EngagementScore)0-30
Priority
=IFS(TotalScore>=25,"Hot",TotalScore>=15,"Warm",TotalScore>=8,"Cool",TRUE,"Cold")Label
Sunset policy automation
Column
Formula
Action
Days since last engagement
=TODAY()-N2Calculate inactivity
Sunset status
=IFS(TODAY()-N2>365,"Remove",TODAY()-N2>180,"Final re-engagement",TODAY()-N2>90,"Re-engagement campaign",TRUE,"Active")Automated sunset workflow
Preparing Email Data for Conditional Analysis
Before applying conditional formulas, your email list needs to be clean and deduplicated. When consolidating contact data from CRM exports (CSV), email marketing platform exports (CSV), event registrations (CSV), web form submissions (CSV), purchased lists and legacy databases, upload the files to Email Extractor to extract and deduplicate email addresses across all sources. Conditional formulas assume each contact appears once in your spreadsheet; duplicate rows produce duplicate scores, duplicate segment assignments and inflated engagement metrics, so deduplication is the first step before applying any conditional logic.
