Should the lookup be the left or right table?

Put the big file on the left and use a left join, so every transaction is kept whether or not it finds a mapping.

Can VLOOKUP handle a file over a million rows?

Not in one sheet: a worksheet stops at 1,048,576 rows. Split the file or use Power Query, the Data Model, pandas or DuckDB.

Why did the join return more rows than the big file?

The lookup table has a duplicate key. Make it unique before joining.

Which option is fastest?

For a one-off on a laptop, DuckDB is usually quickest to write and to run. Power Query is easiest if you need the result inside Excel.

Do I need to sort either file?

No. Hash joins in Power Query, pandas and DuckDB don't require sorted input.

How to join a big CSV to a small lookup table

By the Analistable team · Updated 2026-10-10 · 5 min read

When the main file is too big for a worksheet but the lookup table is small, don't open the big file in Excel. Join it outside the grid: Power Query can merge and load the result to the Data Model or an aggregated table, pandas can read the big file in chunks and merge each chunk with the lookup, and DuckDB can join the CSVs directly with one SQL query.

Part of our guide: How to merge CSV files into one

Why this case is different

Joining two files of similar size is covered in joining two CSV files by column. Here one file is large (say 4 million transaction rows, beyond Excel's 1,048,576-row sheet limit) and the other is a small mapping, such as 300 product codes to categories, or 200 store IDs to regions. The lookup fits in memory comfortably; the big file may not fit in a sheet at all. The trick is to stream or query the big file and keep the small one in memory.

Example inputs
File	Rows	Size	Key
transactions.csv	4,200,000	610 MB	sku
sku_categories.csv	312	9 KB	sku

Prepare the lookup first

Make sure the key is unique in the lookup: one row per sku. A duplicate multiplies rows in the big file.
Trim spaces and match case and type on the key in both files ("00412" vs 412 is the usual culprit).
Keep only the columns you need from the lookup, which reduces the size of the output.

Option 1: Power Query in Excel

Power Query can read a CSV of any length; the row limit only applies when you load the result to a worksheet.

Data > Get Data > From File > From Text/CSV, pick transactions.csv, then Transform Data.
Do the same for sku_categories.csv. Close & Load To… > Only Create Connection for both.
Data > Get Data > Combine Queries > Merge. Choose transactions, then sku_categories, click the sku column in each, Join Kind: Left Outer.
Expand the new column and tick category only.
Either Group By (for example, sum of amount by category and month) and load the small result to a sheet, or Close & Load To… and tick Add this data to the Data Model for a PivotTable over all rows.

Loading 4.2 million rows to a sheet will fail. Aggregating in Power Query or using the Data Model avoids that. The join kinds are explained in Power Query merge join kinds.

Option 2: pandas in chunks

import pandas as pd

lookup = pd.read_csv("sku_categories.csv", dtype={"sku": str})
assert lookup["sku"].is_unique

first = True
for chunk in pd.read_csv("transactions.csv", dtype={"sku": str},
                         chunksize=500_000):
    out = chunk.merge(lookup[["sku", "category"]], on="sku", how="left")
    out.to_csv("transactions_enriched.csv", mode="w" if first else "a",
               header=first, index=False)
    first = False

Each chunk of 500,000 rows is joined to the in-memory lookup and appended to the output, so memory use stays roughly constant. A left join keeps rows with an unknown sku; count those afterwards with category.isna(). If you only need the lookup's one column, chunk["sku"].map(lookup.set_index("sku")["category"]) is a faster alternative to merge.

Option 3: DuckDB, one query

DuckDB reads CSVs directly and joins them without loading everything into RAM first. It's available as a command-line tool and as a Python package.

COPY (
  SELECT t.*, c.category
  FROM read_csv_auto('transactions.csv') AS t
  LEFT JOIN read_csv_auto('sku_categories.csv') AS c USING (sku)
) TO 'transactions_enriched.csv' (HEADER);

-- or just the answer:
SELECT c.category, SUM(t.amount) AS revenue, COUNT(*) AS lines
FROM read_csv_auto('transactions.csv') t
LEFT JOIN read_csv_auto('sku_categories.csv') c USING (sku)
GROUP BY 1 ORDER BY revenue DESC;

Auto-detection may infer sku as a number in one file and text in the other. If the join returns no matches, cast both sides: ON CAST(t.sku AS VARCHAR) = CAST(c.sku AS VARCHAR).

Check the result

Revenue by category after the join (4.2M lines)
category	lines	revenue (£)
Kitchen	1,610,000	2,415,300
Garden	1,302,000	1,887,900
Lighting	1,226,000	1,040,600
(no match)	62,000	71,200
Total	4,200,000	5,415,000

The line counts sum back to 4,200,000, so the join neither dropped nor duplicated rows. The 62,000 unmatched lines are your to-do list for the mapping table.

Analistable uses DuckDB-WASM in your browser, so you can add the large CSV and the lookup and ask "revenue by category" without uploading either. Very large files are limited by your browser's memory. See also merging large CSV files and the CSV merging guide.

Which option to choose

If the people who use the result live in Excel, Power Query is the natural choice: the steps are saved, and next month you replace the file and press Refresh. If you need a new enriched CSV for another system, pandas or DuckDB produce one quickly. DuckDB is the least code and handles files larger than memory well; pandas is better if you already have a Python workflow and want extra cleaning in the same script.

Whatever the tool, keep the lookup table under version control or at least dated. When a category mapping changes, you'll want to know which version produced last quarter's numbers.

Unmatched keys

A left join keeps transactions whose key isn't in the lookup and leaves the new column blank. Don't hide them: list the distinct unmatched keys with their row counts and revenue, sorted largest first. Fixing the top few usually covers most of the value, and you can add a catch-all such as 'Unmapped' in reports until the mapping table is complete.

Frequently asked questions

Should the lookup be the left or right table?: Put the big file on the left and use a left join, so every transaction is kept whether or not it finds a mapping.
Can VLOOKUP handle a file over a million rows?: Not in one sheet: a worksheet stops at 1,048,576 rows. Split the file or use Power Query, the Data Model, pandas or DuckDB.
Why did the join return more rows than the big file?: The lookup table has a duplicate key. Make it unique before joining.
Which option is fastest?: For a one-off on a laptop, DuckDB is usually quickest to write and to run. Power Query is easiest if you need the result inside Excel.
Do I need to sort either file?: No. Hash joins in Power Query, pandas and DuckDB don't require sorted input.