How to Merge Two CSV Files in Excel (VLOOKUP and XLOOKUP)

To merge two CSV files in Excel, import each CSV onto its own sheet, pick the column they share (like a customer ID or SKU), and use XLOOKUP — or VLOOKUP on older Excel versions — to pull columns from the second sheet into the first. This is a join, not a stack: you are matching rows by a shared key, like looking up every order's customer name from a customer list. (Start by converting each file with the free CSV to Excel converter so both land cleanly in one workbook.)

Join vs Stack: Know Which One You Need

People say "merge" for two completely different operations, and using the wrong one corrupts your data:

• Join (this guide): two CSVs share a key column (customer ID, product SKU, employee number). You want columns from file B added beside the matching rows of file A. Example: orders.csv + customers.csv → each order gains the customer's name and region.
• Stack/append: two CSVs have the same columns and you want all rows in one long list. Example: january-sales.csv + february-sales.csv → one combined sales table.

If you need to stack, do not use this guide — use Power Query's append, covered in the combine multiple CSV files guide. Everything below is about joining on a shared key.

Step 1: Import Each CSV to Its Own Sheet

A join needs both tables in one workbook, cleanly separated:

1. Open a blank workbook.
2. Import the first CSV with Data > From Text/CSV into Sheet1. Rename the sheet to something meaningful, like Orders.
3. Import the second CSV the same way into a new sheet. Rename it, e.g. Customers.
4. Confirm each sheet's data starts in row 1 with a header row, no blank rows above it, and no merged cells.

Never paste the second CSV below the first one and try to join sideways — joins work across sheets, not across pasted blocks. Keep the raw imports untouched; do all formula work in additional columns or on a third sheet.

Step 2: Clean the Key Columns First

A join is only as good as its keys. If Orders!A2 contains C-1042 (with spaces) and Customers!A57 contains C-1042, Excel sees two different values and your lookup fails silently.

Before writing any formula, clean both key columns:

1. Remove leading/trailing spaces with =TRIM(A2) in a helper column, then paste-as-values over the original.
2. Check for inconsistent types: a key stored as the number 1042 in one sheet and the text "1042" in the other will never match. Convert both to the same type (select column, Data > Text to Columns, General).
3. Check for case differences only if your keys are case-sensitive — Excel lookups are case-insensitive, so abc matches ABC.

The column mapping guide covers aligning mismatched column structures before a join — useful when the two CSVs came from different systems that name things differently.

Step 3: Join with VLOOKUP (All Excel Versions)

VLOOKUP is the classic join function and works in every Excel version. Say Orders has customer IDs in column A, and Customers has IDs in column A and names in column B. In Orders!B2:

=VLOOKUP(A2, Customers!$A$2:$B$5000, 2, FALSE)

What each part means:

• A2 — the key to look up (this row's customer ID).
• Customers!$A$2:$B$5000 — the table to search. The dollar signs matter: they lock the range so it does not shift when you fill the formula down.
• 2 — return the value from the 2nd column of the search table (the name).
• FALSE — exact match. This is the part people forget. Without FALSE (or 0), VLOOKUP does an approximate match that returns wrong rows silently. For CSV joins, you always want FALSE.

Fill down: double-click the fill handle (the small square at the cell's bottom-right corner) to copy the formula down the whole column. To pull a second column (say, region in column C of Customers), change the 2 to 3:

=VLOOKUP(A2, Customers!$A$2:$C$5000, 3, FALSE)

VLOOKUP's famous limitation: it can only look right. The key column must be the leftmost column of your search range. If the name sits to the left of the ID in the Customers sheet, VLOOKUP cannot reach it — rearrange the columns or use XLOOKUP.

Step 4: Join with XLOOKUP (Excel 2021 / Microsoft 365)

XLOOKUP is VLOOKUP's modern replacement and the better choice if your Excel has it:

=XLOOKUP(A2, Customers!$A$2:$A$5000, Customers!$B$2:$B$5000)

Three arguments: what to find, where to look, what to return. Advantages over VLOOKUP:

• Looks in any direction. The return column can be left or right of the key — no column rearranging.
• Exact match is the default. No FALSE to forget, no silent approximate-match disasters.
• Cleaner multi-column pulls. Each returned column is its own XLOOKUP, and the ranges stay readable.
• Built-in missing-value handling: =XLOOKUP(A2, Customers!$A$2:$A$5000, Customers!$B$2:$B$5000, "Not found") returns "Not found" instead of an error when a key has no match.

For a full row pull, XLOOKUP can return an entire range at once in Microsoft 365:

=XLOOKUP(A2, Customers!$A$2:$A$5000, Customers!$B$2:$D$5000)

This spills the name, region, and tier across three columns from one formula. VLOOKUP needs three separate formulas for the same result.

Step 5: Handle Errors and Duplicates Like a Professional

Wrap in IFERROR (VLOOKUP users). Keys with no match produce #N/A. That is informative during debugging but ugly in a finished table:

=IFERROR(VLOOKUP(A2, Customers!$A$2:$B$5000, 2, FALSE), "No match")

Investigate "No match" rows rather than ignoring them — a missing key often means the two CSVs cover different time periods or one export is incomplete.

Duplicate keys in the lookup table. If Customers lists the same ID twice, both VLOOKUP and XLOOKUP return the first match and ignore the rest. For a customer table that should never happen — deduplicate first (Data > Remove Duplicates, or the duplicate removal guide). But in the Orders sheet, duplicate keys are normal and fine: many orders can share one customer.

Verify the join. After filling down, sanity-check: count the rows (=COUNTA), count the #N/A or "No match" rows, and spot-check five random rows by eye against the source sheet. A join that is 99% right and 1% silently wrong is worse than one that fails loudly.

VLOOKUP vs XLOOKUP: Comparison

Practical rule: write XLOOKUP if everyone who touches the file has Microsoft 365 or Excel 2021+. Write VLOOKUP if the workbook might be opened in Excel 2019 or earlier, and double-check that FALSE.

When the Join Breaks: Troubleshooting

Every row returns #N/A. The keys do not match — almost always a type or spacing issue from Step 2. Test one key manually: =A2=Customers!A57 should return TRUE for a known pair. If it returns FALSE, the values differ invisibly (spaces, text-vs-number).

Some rows match, others do not. Partial key inconsistency — a few rows have trailing spaces or different formatting. Use TRIM on both key columns and re-check types.

VLOOKUP returns the wrong name, not an error. You omitted FALSE and got an approximate match, or your column index counts the wrong column. Add FALSE first; it fixes most "wrong value" cases.

Formulas return values but they are stale. Someone sorted or edited the lookup sheet after you filled down. Formulas recalculate automatically, so staleness usually means the values were pasted as static text at some point. Re-enter the formula in the top cell and fill down again.

The workbook crawls. VLOOKUP/XLOOKUP over full-column references (A:A) on tens of thousands of rows is slow. Restrict ranges to the actual data ($A$2:$A$5000), or convert both sheets to Excel Tables so ranges stay tight and auto-expand.

You actually needed to stack, not join. If both CSVs have identical columns and you forced a "join", the result is nonsense. Undo, and append instead — Power Query's Append Queries stacks them correctly in seconds.

Circular "match" between three files. Join A→B, then B→C works. Trying to join all three at once in one formula does not. Chain the lookups: bring B's columns into A, then look up C from the combined table.

FAQ

Can I join on two columns, like first name + last name?

Yes — create a helper key in both sheets with =A2&"|"&B2, then join on that single helper column. The pipe separator prevents accidental collisions ("Ann|Lee" vs "An|nLee" style edge cases are rare but the separator makes the intent clear).

How many rows can I join?

Excel handles hundreds of thousands of rows, but performance degrades with full-column lookups. For very large joins, Power Query's Merge is dramatically faster than worksheet formulas.

Will the join update if the source CSV changes?

If both tables live in the workbook as imported data, formulas recalculate when you re-import. If the CSVs change monthly, consider a Power Query merge that refreshes with one click instead of re-importing by hand.

What if there is no shared key column?

Then you cannot join reliably. Look for a composite key (name + date, email, order number) present in both files. Joining on names alone is risky — duplicates are almost guaranteed.

Should I use VLOOKUP or INDEX/MATCH?

INDEX/MATCH was the classic workaround for VLOOKUP's left-lookup limitation before XLOOKUP existed: =INDEX(Customers!B:B, MATCH(A2, Customers!A:A, 0)). It still works everywhere VLOOKUP does. But if your Excel has XLOOKUP, use XLOOKUP — it is shorter, clearer, and defaults to exact match. INDEX/MATCH remains relevant only for workbooks shared with very old Excel versions.

Can I join without a shared key, using row position?

Technically yes — =Customers!B2 copied down pulls the value from the same row number. But this is fragile: if either CSV gains or loses a row, every row below the change misaligns silently. Only do this when you are certain both files have identical row order and count, and even then, add a spot-check column comparing a second field.

Joining Three or More CSVs

Real reporting often needs three sources — say orders, customers, and a product catalog. Do not try to join all three in one mega-formula. Chain the joins:

1. Join B into A first. Use XLOOKUP to pull customer columns into the Orders sheet, exactly as in Steps 3–4. Verify with the checklist below.
2. Paste the results as values (optional but wise). Select the new formula columns, copy, Paste Special > Values. This freezes the first join so the second join cannot cascade errors through two layers of live formulas.
3. Join C into the combined table. Now XLOOKUP the product columns from the catalog sheet using the SKU key. Verify again.
4. Keep each source on its own sheet. Three source sheets plus one combined sheet is clean and auditable. A workbook where everything is pasted into one grid is where joins go to die.

An alternative for heavy multi-file work is Power Query's Merge, which handles three-way joins in a visual interface and refreshes when source CSVs update. The rule of thumb: two files and a one-off task → worksheet formulas; three or more files, or a monthly repeat → Power Query.

One more consideration: join order affects error diagnosis. Join the smallest, cleanest lookup table first — errors surface earlier when the intermediate result is small enough to eyeball.

60-Second Checklist

• Both CSVs imported to separate, clearly named sheets.
• Key columns cleaned: TRIM applied, same data type both sides.
• VLOOKUP uses FALSE (or XLOOKUP used instead).
• Ranges locked with $ or built as Tables; no full-column references.
• #N/A rows investigated, not ignored; duplicates in lookup table removed.
• Spot-checked five rows by eye; row counts match expectations.