Enrich Lead Lists in Google Sheets vs Paid Enrichment Tools
Enrich lead lists in Google Sheets: turn a 3-column export into a 14-column prospect sheet with formulas and no per-record fees. Copy the template.
You run outbound for a small software team, and 300 new leads land in your sheet every Monday. Each row has a name, a company, and a website, and nothing else. Looking every one of them up by hand takes about 6 hours, so most rows never get a personalized email.
Paid enrichment tools solve this for teams with budget and someone to manage the integration. Formulas solve it for teams with neither. Here is the first one. It runs down the whole column.
=AITEXT("What is the current job title of " & A2 & " at " & B2 & "? Reply with the title only. If you cannot find it, reply Not found.", , TRUE)The third argument turns on web search, so the model looks the person up before it answers. Works with OpenAI, Claude, and Perplexity models picked in the sidebar.
What comes back
| A (Name) | B (Company) | D (=AITEXT result) |
|---|---|---|
| Dana Whitfield | Northwind Logistics | VP of Operations |
| Marcus Oyelaran | Brightline Dental Group | Director of Patient Experience |
| Priya Raman | Kestrel Analytics | Head of Revenue Operations |
| Tom Alvarez | Harbor Point Roofing | Not found |
Drag that formula down 300 rows and go get coffee. One prompt, 300 lookups, and a title column you did not have to type. That is the whole idea of this post. A bare lead list becomes a research sheet one column at a time, and by the end you have 14 columns, a quality score, and a template you can import. New to the add-on? The getting started guide takes five minutes.
Quick verdict
| Option | Best for | Price shape | Data source | Setup |
|---|---|---|---|---|
| SheetMagic formulas | Teams that live in Sheets and want to define every field themselves | Monthly plan with a usage allowance, no per-record fee | Live web pages, LinkedIn company pages, job boards, any AI model | Install the add-on, paste formulas |
| Apollo | Teams that want a contact database and email sequencing in one place | Free Starter plan, then paid plans by seat count, with monthly credit limits | Its own database, 240M+ contacts and 30M+ companies | Sign up, search, export |
| ZoomInfo | Sales, marketing, and recruiting teams buying through a sales process | No public prices. Contact sales or start a free trial | Its own database, 420M+ contacts and 145M+ companies | Demo, then one-click export to your CRM |
| Clearbit | HubSpot customers | Clearbit has joined HubSpot. Pricing is not published on clearbit.com | Its own database | Through HubSpot |
What paid enrichment tools do well
They have a database you can search before you have a single name. Apollo's site puts it at 240M+ contacts and 30M+ companies, filtered by job title, seniority, company size, industry, and location. ZoomInfo's says 420M+ contacts, 145M+ companies, and 300+ attributes including technology stack and funding.
They connect to the CRM. Apollo describes syncing contact data and outreach campaigns with Salesforce and HubSpot. ZoomInfo describes one-click export and automatic record sync with major CRMs.
They send as well as enrich. Apollo includes built-in email sequencing, and ZoomInfo sells an Engage product alongside its data, offered by demo or free trial.
Where they fall short
The price scales with records. Apollo meters usage in credits with a monthly limit, and export credits are spent every time a contact leaves the tool. The month's allowance runs out right when the event list arrives.
The fields are whatever the database holds. If your playbook needs "does this company use HubSpot" or "what did they announce last quarter," you write that question yourself, and a database has no box for it.
The data is only as fresh as the last refresh. ZoomInfo describes continuous verification, and Apollo does not say. A formula that reads the company's website today needs no refresh cycle at all.
And the list lives in their tool. Every workflow starts with an export.
How SheetMagic handles the same job
Put one formula at the top of each new column, reference the columns you already have, and drag down. Each column is one question about the lead.
Columns A to C are your raw list. Everything after that is a formula.
| Column | Field | Formula |
|---|---|---|
| A | Name | your data |
| B | Company | your data |
| C | Website | your data |
| D | Job title | AITEXT with web search |
| E | Seniority | AITEXT |
| F | Homepage text | VISIT |
| G | Industry | AITEXT reading F |
| H | Tech stack | AITEXT reading F |
| I | Recent news | AITEXT with web search |
| J | Hiring signal | AITEXT with web search |
| K | Talking point | AITEXT reading D, G, I |
| L | Pain hypothesis | AITEXT reading G, H, J |
| M | Score | native COUNTA |
| N | Duplicate | native COUNTIF |
Layer 1: company info from the homepage
VISIT pulls the readable text of a page into a cell. Then two AITEXT formulas read that cell and answer specific questions.
=VISIT(C2)Column F. Fetches the homepage as clean text, up to 50K characters. The cell shows a loading placeholder first and fills in within about a minute.
=AITEXT("From this homepage text, give the industry and what the company sells in under 15 words. If unclear, reply Not found.", F2)Column G. Reads the text in F2, so there is no second fetch and no second credit.
=AITEXT("List every software product, platform, or integration named in this text, comma separated. If none, reply None.", F2)Column H. Tech stack from the same text.
What comes back
| C (Website) | G (Industry) | H (Tech stack) |
|---|---|---|
| northwindlogistics.com | Freight logistics, regional LTL trucking and warehousing | Samsara, QuickBooks, HubSpot |
| brightlinedental.com | Multi-location dental group, general and cosmetic dentistry | Dentrix, Weave, Google Reviews |
| kestrelanalytics.io | B2B analytics software for e-commerce brands | Shopify, Snowflake, Slack, Stripe |
One VISIT per company, then as many AITEXT questions as you want against that one cell. The AI columns spend tokens, not credits, and short prompts on a small model are cheap. Pick GPT-4o mini, Claude Haiku, or Gemini Flash in the sidebar for these classification jobs.
Keep Formula is on by default. Once a result lands, the value is frozen and the formula is kept as text with a leading apostrophe, so the sheet never re-runs it on recalculation. You pay for each lookup once.
If your list has LinkedIn company URLs, add employee counts from a side tab. LINKEDINCOMPANY takes a whole range and spills one row per company.
=LINKEDINCOMPANY(Companies!A2:A301)Runs in the background and writes the table below the formula within about a minute. Put it on its own tab so the spill has room.
What comes back
| Company Name | Industry | Employees | Headquarters | Website |
|---|---|---|---|---|
| Northwind Logistics | Transportation | 210 | Tacoma, WA | northwindlogistics.com |
| Brightline Dental Group | Health care | 340 | Las Vegas, NV | brightlinedental.com |
| Kestrel Analytics | Software | 48 | Denver, CO | kestrelanalytics.io |
A VLOOKUP on the website column pulls Employees back into the main sheet.
Layer 2: contact info (role, seniority)
The opening formula already filled column D with a job title. Seniority is a classification, so it needs no web search.
=AITEXT("Classify this job title as one of: C-level, VP, Director, Manager, Individual contributor. Reply with the label only.", D2)Column E. A fixed label set means you can filter and count on it later.
What comes back
| D (Job title) | E (Seniority) |
|---|---|
| VP of Operations | VP |
| Director of Patient Experience | Director |
| Head of Revenue Operations | Director |
| Not found | Individual contributor |
When you only have the company
Sometimes the export has a domain and no name at all. FINDLEADS searches for people by title at a domain, in a location, or in an industry, and spills a table.
=FINDLEADS({"VP of Sales","Head of Sales"}, , C2, , 5)Up to 5 sales leaders at the domain in C2. Background function, about a minute. Run it in a side tab so the spill has room.
What comes back
| Full Name | Job Title | Seniority | Company | ||
|---|---|---|---|---|---|
| Elena Marsh | VP of Sales | VP | e.marsh@northwindlogistics.com | linkedin.com/in/elenamarsh | Northwind Logistics |
| Devon Hale | Head of Sales, West | Director | d.hale@northwindlogistics.com | linkedin.com/in/devonhale | Northwind Logistics |
| Ravi Sethi | Sales Director | Director | Not found | linkedin.com/in/ravisethi | Northwind Logistics |
Then check the emails before anyone sends. VALIDATEEMAIL takes the whole column.
=VALIDATEEMAIL(Leads!D2:D301)Flags undeliverable, disposable, and role-based addresses. Background function. One formula for the whole column.
What comes back
| Result | Quality | Detail | |
|---|---|---|---|
| e.marsh@northwindlogistics.com | deliverable | high | mailbox exists |
| d.hale@northwindlogistics.com | deliverable | high | mailbox exists |
| info@harborpointroofing.com | risky | low | role-based address |
Layer 3: intent signals (news, hiring)
Now the columns that make an email timely. The pattern is the same as the opening formula: AITEXT with web search on, asking one narrow question.
=AITEXT("In two sentences, what is the most recent public news about " & B2 & " in the last 6 months? Cover funding, hiring, leadership changes, or launches, and include the month. If nothing is found, reply Not found.", , TRUE)Column I. Asking for the month lets you sort by recency later.
=AITEXT("Is " & B2 & " currently hiring for sales, marketing, or operations roles? Reply Yes or No, then one job title if Yes.", , TRUE)Column J. A hiring company has budget and a problem. That is a reason to write this week.
What comes back
| B (Company) | I (Recent news) | J (Hiring signal) |
|---|---|---|
| Northwind Logistics | Opened a fourth Tacoma terminal in July 2026 and is hiring 40 dispatchers. Announced a customer tracking portal in August 2026. | Yes, Operations Manager |
| Brightline Dental Group | Acquired two Las Vegas practices in June 2026, bringing the group to 22 locations. No funding news found. | Yes, Regional Marketing Manager |
| Kestrel Analytics | Raised a Series A in May 2026 and added a Snowflake integration in August 2026. | Yes, Account Executive |
For your top 30 accounts, replace the one-cell answer with the full picture. JOBSEARCH returns every open role across Indeed, LinkedIn, Glassdoor, and ZipRecruiter as a table.
=JOBSEARCH(B2, "US", , , , 20)Up to 20 openings for the company name in B2. Background function. Columns include title, location, posted date, salary range, and platform.
What comes back
| Title | Company | Location | Posted | Platform |
|---|---|---|---|---|
| Operations Manager | Northwind Logistics | Tacoma, WA | 2026-08-28 | Indeed |
| Dispatcher (Night) | Northwind Logistics | Tacoma, WA | 2026-08-26 | ZipRecruiter |
| Fleet Coordinator | Northwind Logistics | Seattle, WA | 2026-08-19 |
Layer 4: personalization (talking points, pain hypotheses)
The last two columns turn research into an opening line. They reference the columns you already filled, so they need no web search at all. For more prompt patterns like these, see the 50 prompt templates.
=AITEXT("Write one sentence a salesperson could open an email with, referencing this: Title: " & D2 & ". Industry: " & G2 & ". News: " & I2 & ". No flattery, no exclamation marks.", )Column K. Specific because it reads three specific cells.
=AITEXT("Given this industry, tech stack, and hiring signal, name the single most likely operational pain this company has right now, in under 20 words. Industry: " & G2 & ". Stack: " & H2 & ". Hiring: " & J2, )Column L. A hypothesis, not a fact. Label it that way in the email.
What comes back
| A (Name) | K (Talking point) | L (Pain hypothesis) |
|---|---|---|
| Dana Whitfield | Congrats on the fourth Tacoma terminal, 40 new dispatchers is a lot of onboarding for an ops team. | Dispatcher onboarding and shift scheduling across four sites with a QuickBooks-era back office. |
| Marcus Oyelaran | Two new Las Vegas practices in June means 22 locations sharing one patient experience playbook. | Keeping review response and recall reminders consistent across 22 practices on Weave. |
| Priya Raman | A Series A and a Snowflake integration in the same summer usually means the sales team is about to grow. | New AEs need a repeatable outbound motion before the Series A runway gets spent. |
Score the data before anyone emails it
Two native formulas keep bad rows out of the sequencer.
=COUNTA(D2:L2) - COUNTIF(D2:L2, "Not found") - COUNTIF(D2:L2, "None")Column M. Counts filled research cells and subtracts the misses. A row scoring 7 or more is ready. Below 4, skip it or look it up by hand.
=IF(COUNTIF($A$2:$A$301, A2) > 1, "Duplicate", "")Column N. Flags any name that appears twice. Event lists and LinkedIn exports overlap more than you would think.
What comes back
| A (Name) | M (Score) | N (Duplicate) |
|---|---|---|
| Dana Whitfield | 9 | |
| Marcus Oyelaran | 9 | |
| Priya Raman | 9 | |
| Tom Alvarez | 3 | Duplicate |
Filter on Score and Duplicate, and the 300-row list becomes the 180 rows worth a personalized email.
Every formula in this post runs on the free plan. Free comes with 20,000 AI tokens and 100 integration credits, one time, no card. That covers the AI columns on a few dozen rows and about 100 page fetches. If you enrich a list like this every week, Solo gives you 3.25M tokens and 10K credits a month, which is thousands of leads. See pricing.
Side by side on a real workflow
The task: 300 leads from a trade show, enriched and ready for a sequencer.
| Step | Paid enrichment tool | SheetMagic in Sheets |
|---|---|---|
| 1 | Export the badge scan to CSV | Paste the badge scan into columns A to C |
| 2 | Import into the tool, map columns | Paste the 11 formulas from the template into row 2 |
| 3 | Run enrichment, spend 300 credits | Drag row 2 down, wait about a minute for the background functions |
| 4 | Export the enriched CSV | Filter on Score and Duplicate |
| 5 | Import into Sheets or the CRM | Done, it is already in Sheets |
| 6 | Add custom fields by hand or with a second tool | Add a column and write another prompt |
The tool wins step 1 when you have no list at all. The sheet wins everywhere else, and step 6 is the one that matters. Your playbook changes every quarter, and a new question is a new column, not a support ticket.
When to still use a paid enrichment tool
Use one when you are starting from zero and need a searchable database of people, not just companies. FINDLEADS helps, but a full database is deeper.
Use one when CRM sync is the whole point and nobody on the team will touch a sheet. Apollo names Salesforce and HubSpot on its site.
Use one when you need direct phone numbers at scale. Apollo sells mobile credits, and ZoomInfo advertises 120M+ direct dials. Website scraping finds the main line, not the direct dial.
For everything else, the sheet is cheaper, more current, and yours. More lead-gen workflows are on the lead generation page.
Copy this template
Download the template CSV. Import it into a blank sheet (File, Import, Upload) and the formulas are ready to drag down.
| Column | Header | Row 2 |
|---|---|---|
| A | Name | your data |
| B | Company | your data |
| C | Website | your data |
| D | Job title | =AITEXT("What is the current job title of " & A2 & " at " & B2 & "? ...", , TRUE) |
| E | Seniority | =AITEXT("Classify this job title as one of: ...", D2) |
| F | Homepage text | =VISIT(C2) |
| G | Industry | =AITEXT("From this homepage text, give the industry ...", F2) |
| H | Tech stack | =AITEXT("List every software product ...", F2) |
| I | Recent news | =AITEXT("... most recent public news about " & B2 & " ...", , TRUE) |
| J | Hiring signal | =AITEXT("Is " & B2 & " currently hiring ...", , TRUE) |
| K | Talking point | =AITEXT("Write one sentence ... " & D2 & ... & I2, ) |
| L | Pain hypothesis | =AITEXT("... " & G2 & ... & J2, ) |
| M | Score | =COUNTA(D2:L2) - COUNTIF(D2:L2, "Not found") - COUNTIF(D2:L2, "None") |
| N | Duplicate | =IF(COUNTIF($A$2:$A$301, A2) > 1, "Duplicate", "") |
Put LINKEDINCOMPANY, FINDLEADS, VALIDATEEMAIL, and JOBSEARCH on their own tabs. They spill tables, and a spill needs empty cells below and to the right.
What to watch for
- Background functions (
VISIT,LINKEDINCOMPANY,FINDLEADS,VALIDATEEMAIL,JOBSEARCH) show a loading placeholder first and fill in within about a minute. The formula is not broken. - Spilled tables land one row below the formula cell. Leave that space empty or Sheets shows a #REF! error.
- Web search and scraping only see public pages. "Not found" is a real answer, and the score column exists so you notice it.
- Background scrapers bill a few credits per row returned, so set the last argument (the row limit) on purpose.
Start with the job title column on your next 20 leads. Once that works, add one column a day until the sheet matches the template.
Wrap-up
Spreadsheets shouldn't limit what you can do. SheetMagic brings AI and web scraping to your workflow, whether you're generating content, scraping data, or automating repetitive tasks.
If that sounds like the kind of tooling you want to use, try SheetMagic or watch our tutorials.