You have a fresh lead list and a CRM export, and you need to know which people are new, which are already in, and which changed. Eyeballing two sheets does not scale past a few dozen rows. These five formulas do it in Google Sheets with no add-ons. Put the new list on a tab called New and your CRM export on a tab called CRM, with email in column B on both.
1. Build a match key on both tabs
"Jane@Acme.com " and "jane@acme.com" are the same person, but a plain lookup says they are not. Add a key column (Z here) to each tab.
=LOWER(TRIM(B2))
Fill it down on both New and CRM, and match on Z from now on, never on the raw email.
2. Flag who is missing from the CRM
On the New tab, one column tells you whether each row already exists.
=IF(Z2="","NO EMAIL",IF(COUNTIF(CRM!$Z:$Z,Z2)=0,"MISSING","IN CRM"))
Run the same formula on the CRM tab pointed at New!$Z:$Z to see the reverse: contacts you have that the new list dropped.
3. Pull every missing row onto its own tab
Filtering by hand is where rows get lost. This spills the full missing rows into a new tab in one go.
=FILTER(New!A2:F,New!Z2:Z<>"",COUNTIF(CRM!Z2:Z,New!Z2:Z)=0)
Adjust A2:F to cover your columns. This tab is what you import, so nothing already in the CRM gets created twice.
4. Fall back to name plus company when email is blank
Rows with no email still need a key. Strip punctuation and common company suffixes so "Acme, Inc." and "acme inc" line up.
=LOWER(TRIM(A2))&"|"&TRIM(REGEXREPLACE(REGEXREPLACE(LOWER(C2),"[.,]",""),"\b(inc|llc|ltd|corp|co|gmbh)\b",""))
A is the full name and C is the company here. Use this key only for the NO EMAIL rows from step 2, and review those matches by eye, because two people can share a name.
5. Spot changed fields on rows that already exist
A contact can be in your CRM with an old phone number or title. Compare one field at a time; D is the phone column on both tabs in this example.
=IFERROR(IF(TRIM(XLOOKUP(Z2,CRM!$Z:$Z,CRM!$D:$D))=TRIM(D2),"SAME","CHANGED"),"NOT IN CRM")
Filter to CHANGED and decide row by row whether the new value should overwrite the old one. Do not bulk-overwrite; sometimes the CRM value is the one a rep fixed by hand.
Run all five on copies of both files, import only the step 3 tab as new contacts, and handle the CHANGED rows as updates.
Before you import the missing contacts, clean them. Drop your step 3 tab into the free CSV preflight checker to see what will break, right in your browser. Or send up to 200 rows to the $25 Quick Clean and get back a cleaned CSV (same-email duplicates merged, possible duplicates flagged) plus a change log within 24 hours, no tool login needed. Prefer to do it yourself? The HubSpot/Salesforce Import Checklist ($9) has every pre-import check on one page. Bigger lists and pricing at pureservices.store.
Top comments (0)