Dataset Join & VLOOKUP: Merge Two Datasets or Files by Key

Dataset Join & VLOOKUP joins two tables on key fields, like a SQL JOIN or an Excel VLOOKUP. Each table can be an Apify dataset, a public CSV, Excel or JSON file, or a JSON list you paste in. You pick the key (email, sku, website, or several fields at once) and the join type. Every left row gets the matching right row's fields, or you keep only the rows that match, or only the rows that are missing from the other list. Each output row says whether it found a match (_matched) and how many right rows share its key (_matchCount).

Why this Actor

• Every common join. Left (VLOOKUP), inner, right, full, anti (left rows with no match) and right anti (right rows with no match).
• Keys that match in real data. Letter case and extra spaces are ignored by default, so BEN@Globex.io matches ben@globex.io. Optional: compare digits only (phone numbers written as (555) 010-2030 or 555.010.2030) or compare URLs loosely (https://www.acme.com/ matches acme.com). A number and the same number as text match (1001 and "1001"), which matters when one side comes from a CSV file.
• Composite keys. Match on several fields at once, such as first name + last name + city. Dots reach nested fields (company.domain).
• You decide what happens with duplicates. When several right rows share a key, take the first, the last, or all of them (one output row per matching pair, as in SQL).
• No silent overwrites. When both sides have a field with the same name, the right one is renamed (name → right_name). You can pick which right fields to bring over, prefix all of them, or let right values replace left ones.
• Files without a conversion step. CSV, TSV, Excel (XLSX, XLS) and JSON / JSON Lines files are read straight from a public link, including Google Sheets, Google Drive and Dropbox share links. Downloads to private or internal network addresses are refused.
• Streams the big side. Only the right table is held in memory. The left table is read row by row, so it can be as big as you like.

Use cases

• Enrich leads with CRM data: left = scraped leads, right = a CRM export; key email. Each lead gets its CRM status and owner, and _matched: false shows the new ones.
• Add Google Maps details to contact results: left = Website Contact Info Scraper results, right = a Google Maps scrape; match the left url with the right website with Compare URLs loosely on, so https://www.cafe.example/ and cafe.example match.
• Find rows missing from another list (anti join): which scraped companies aren't in your CRM yet, which products are missing from a supplier feed, which emails aren't on your suppression list.
• Match product feeds by SKU: join your catalogue with a supplier price list or stock file on sku (or sku + size), and use All matches when a SKU has several offers.

How to use it

1. Left table: pick a dataset, or give a public file URL, or paste JSON items. This is the table you enrich; put the bigger table here.
2. Right table: the same choices. This is the lookup table, held in memory; put the smaller table here.
3. Left key fields: for example email. If the right table names it differently (Email Address), set Right key fields in the same order.
4. Choose the Join type and what to do when several right rows match. Adjust the key matching options if needed.
5. Run it. The joined rows are in the run's dataset (export as CSV, Excel or JSON, or chain an integration), and the counts are in the key-value store record OUTPUT.

If a side has more than one source filled in, the dataset is used first, then the file URL, then the JSON items (the log says which one was ignored).

Input example

{
  "leftDatasetId": "aBcDeFgHiJkLmNoPq",
  "rightFileUrl": "https://docs.google.com/spreadsheets/d/1AbC.../edit#gid=0",
  "leftKeys": ["email"],
  "rightKeys": ["Email Address"],
  "joinType": "left",
  "multipleMatches": "first",
  "rightFields": ["status", "owner"],
  "caseInsensitive": true,
  "trimWhitespace": true
}

Field	Notes
Left / right dataset	A dataset ID, or {{resource.defaultDatasetId}} in an integration.
Left / right file URL	A public CSV, TSV, XLSX, XLS, JSON or JSON Lines file, up to 500 MB. The first row is the header; for Excel, the first sheet is read. CSV values are read as text.
Left / right JSON items	A JSON array of objects.
Left key fields / Right key fields	One or more fields that must all match. Right key fields default to the left names.
Join type	left (default), inner, right, full, anti, rightAnti. See the table below.
When several right rows match	first (default), last, or all.
Ignore letter case / Ignore extra spaces	On by default.
Compare digits only	For phone numbers. Off by default.
Compare URLs and domains loosely	Ignores http(s)://, www., the query, the fragment and trailing slashes. Off by default.
Right fields to add	Default: all right fields except the right key fields.
Prefix for clashing right fields	Default right_. "" lets right values replace left values.
Prefix every right field	Off by default.
Add _matched and _matchCount	On by default.
Max output rows	Optional cap.
Max right-table rows	Default 1,000,000. A bigger right table stops the run with a clear message, before anything is output or charged.

Join types

With the example input that's filled in when you open the Actor (4 leads on the left, 3 CRM records on the right, joined on email; Ana and Ben match, Ben thanks to the case and space normalisation):

Join type	Output	Example result
Left (VLOOKUP)	Every left row; matched rows get the right fields	4 rows: Ana and Ben with CRM fields, Cara and Dan unmatched
Inner	Only left rows with a match	2 rows: Ana, Ben
Right	Matched rows, plus right rows with no match	3 rows: Ana, Ben, Eve
Full	Matched rows, plus unmatched rows from both sides	5 rows
Anti	Only left rows with no match, unchanged	2 rows: Cara, Dan
Right anti	Only right rows with no match, unchanged	1 row: Eve

Output example

The left join from the example input:

[
  { "email": "ana@acme.com", "name": "Ana", "status": "customer", "owner": "Kim", "_matched": true, "_matchCount": 1 },
  { "email": "BEN@globex.io", "name": "Ben", "status": "trial", "owner": "Lee", "_matched": true, "_matchCount": 1 },
  { "email": "cara@initech.com", "name": "Cara", "_matched": false, "_matchCount": 0 },
  { "email": "dan@hooli.com", "name": "Dan", "_matched": false, "_matchCount": 0 }
]

• A joined row is the left row (all its fields, values unchanged) plus the right row's fields. The key values in the output are the original ones, not the normalised ones.
• Unmatched left rows keep just their own fields (no empty right columns are added).
• In right and full joins, an unmatched right row gets its key value in the left key column (email above), then its other fields named as in joined rows. In right anti joins, right rows are output unchanged.
• _matchCount is the number of right rows with that key, also when only the first or last one is used.

The key-value store record OUTPUT holds the counts:

{
  "joinType": "left",
  "multipleMatches": "first",
  "leftSource": "items",
  "rightSource": "items",
  "leftRows": 4,
  "rightRows": 3,
  "matchedLeftRows": 2,
  "unmatchedLeftRows": 2,
  "leftRowsMissingKey": 0,
  "matchedRightRows": 2,
  "unmatchedRightRows": 1,
  "rightRowsMissingKey": 0,
  "duplicateRightKeys": 0,
  "distinctRightKeys": 3,
  "joinedRows": 2,
  "unmatchedRows": 2,
  "outputRows": 4,
  "stoppedBy": null
}

duplicateRightKeys counts right rows whose key already appeared earlier in the right table. stoppedBy is "max results" or "max cost" when the run stopped early (the row counts then cover what was read until that point).

How much does it cost?

Pay per event, Apify platform usage included:

• $0.002 per joined row (joined-row, $2.00 per 1,000): each output row that combines a left row and a right row.
• $0.001 per unmatched row (unmatched-row, $1.00 per 1,000): each output row passed through without a match (unmatched rows in left, right and full joins, and every row of an anti or right anti join).

Apify Store discounts: the prices above are for Apify's Free plan. Paid plans pay less: per joined row $0.0018 on Starter, $0.0016 on Scale, $0.0014 on Business; per unmatched row $0.0009, $0.0008 and $0.0007. The "from" price at the top of the Store page is the Business price.

Rows that aren't output cost nothing: reading the tables, left rows dropped by an inner join, right rows that aren't output. Problems with the input (a missing key field, a dataset that can't be opened, a file that can't be downloaded or read, a right table over the row limit) are found before anything is output: the run fails with a plain-English message and nothing is charged for rows. Only a left file that turns out to be damaged partway through stops the run after some rows are out; those rows stay in the dataset and are charged.

Worked examples:

Examples use the Free-plan rate. Where an example doesn't add it, each run also has its run-start charge, counted per GB of Actor memory (minimum one; at the default 1 GB memory it's one); Apify Proxy (where used) is billed separately.

• 10,000 leads left-joined with a CRM export, 6,000 matched: 6,000 × $0.002 + 4,000 × $0.001 = $16.00.
• The same tables as an inner join (only the 6,000 matches): 6,000 × $0.002 = $12.00.
• An anti join of 20,000 scraped companies against your CRM, 1,500 not in it yet: 1,500 × $0.001 = $1.50.
• 500 products joined on SKU with a supplier feed that has 3 offers per SKU, with All matches: 1,500 × $0.002 = $3.00.

If you set a maximum cost per run, the Actor stops cleanly at that limit and stores only the rows that were paid for.

FAQ and limits

Is the matching fuzzy?

No. Keys must be equal after the normalisation you chose (letter case, extra spaces, digits only, loose URLs). Acme Inc and Acme Incorporated don't match, and neither do +1 555 010 2030 and 555 010 2030 with digits only (the country code is a digit). To match phone numbers reliably, format both sides to E.164 first with the Phone Number Validator & Formatter.

What happens to rows with an empty key?

A row whose key field is missing, null, empty or only spaces (or has no digits when Compare digits only is on) never matches anything, not even another empty key. It is treated as unmatched: kept in left, full and anti joins (and right, full and right anti joins for right rows), dropped from inner joins. leftRowsMissingKey and rightRowsMissingKey count them. With several key fields, all of them need a value.

Which table should be on the left?

The bigger one. The right table is held in memory (only its key and the fields you bring over, when you set Right fields to add), while the left table is streamed. In our test, a run with a million right rows of five short fields peaked at about 700 MB, so the default 1 GB run fits them; wide rows (dozens of fields, long text) take several times more. If the run runs out of memory, give it more (up to 8 GB), or list only the columns you need under Right fields to add. An anti join keeps only the right keys in memory.

How do First, Last and All work with right and full joins?

First and Last pick one right row per left row; the other right rows with the same key are counted as matched and aren't output. For SQL behaviour, where every matching pair is a row and every right row appears, choose All matches.

Are numbers and text the same key?

Yes. Keys are compared as text, so the numbers 1001 and 1001.0 match the text "1001", and true matches "true". Lists and objects are compared as their whole JSON text.

Which file formats work?

CSV, TSV and other delimited text (the delimiter and encoding are detected), Excel XLSX/XLSM and legacy XLS (first sheet, cached formula results, macros never run), JSON (a list of objects, or an object with one list of objects inside) and JSON Lines. Files must be public links (no sign-in), up to 500 MB. OneDrive and SharePoint links aren't supported. To read other sheets, or to clean up a file first, convert it with CSV & Excel to Dataset and join the resulting dataset.

What if the run is restarted?

After a migration or restart, the Actor rebuilds the join, skips the rows already in its dataset and continues, so no row is stored or charged twice. This relies on the inputs not changing in between.

Other limits

• Right fields to add doesn't apply to anti and right anti joins: those output the original rows unchanged.
• Rows are output in left-table order; in right and full joins, the unmatched right rows come last, in right-table order.
• Apify stores items of up to 9 MB; a joined row is the sum of both rows, so very wide rows can hit that limit.

Related Actors

Actor	Use it to
HTTP Request & API Calls	Fetch the other side of the join from a REST API, one request per row or paginated.
Merge & Deduplicate Datasets	Stack several datasets into one and remove duplicates before or after joining.
Dataset Filter & Transform	Filter the joined rows (for example _matched is false), rename or drop columns, or flatten nested fields.
CSV & Excel to Dataset	Turn spreadsheets with several sheets, header rows further down or other options into a dataset to join.
Website Contact Info Scraper	Get emails, phones and social profiles from company websites, then join them with your other lead data.
Phone Number Validator & Formatter	Format phone numbers to E.164 on both sides so they join reliably.
Google Sheets Sync	Send the joined rows to a Google Sheet (append, replace or upsert).

Spreadsheet files are read with permissively licensed open-source software: openpyxl (MIT) for XLSX/XLSM, xlrd (BSD) for XLS, and defusedxml (PSF) to read workbook XML safely.