Join CSV Files with a Free CSV Join in Google Sheets

Introduction

Your orders sit in one export and the customer details sit in another. Answering a simple question about revenue by city needs the two side by side.

A join combines the files on a shared key. A spreadsheet can do the same work without extra software, as long as the key is consistent and the columns are planned.

This guide builds each join type with native Google Sheets functions, then shows where an exact key stops being enough.

How a Join Works in a Spreadsheet

Every join answers three questions:

• Which key: the column that identifies a row on each side, such as an order ID or an email address.
• Which columns: the fields to bring across, chosen from the second tab.
• What happens to unmatched rows: whether a row with no key on the other side survives, and from which tab.

The third question is the difference between an inner, left, right and full outer join. All four use the same lookup; only the filter around it changes.

Matching is exact. Nairobi and nairobi are different keys, and so are an address with a trailing space and the same address without one. Clean the key before joining rather than after.

Import Both Files as Tabs

Create one spreadsheet and import the first file into a tab called Orders and the second into a tab called Customers. Keep the header row at the top of each tab and the data underneath.

Give the key column the same name on both tabs. The name itself does not matter to the formulas, but a consistent name makes the result easier to read and stops a column being matched by accident.

Use File, then Import, then Upload, and choose Insert new sheet so the import does not overwrite the tab you are working on. Repeat for the second file.

Before writing a formula, trim both key columns. A stray space at the end of an email is invisible on screen and turns a match into a miss for every lookup below.

A Left Join with XLOOKUP

A left join keeps every row from the first tab and adds the columns from the second tab where the key matches. On the Orders tab, put this formula in the first empty column beside the data:

=XLOOKUP($B2, Customers!$A:$A, Customers!$B:$B, "no match")

The first argument is the order email, the second is the key column on the Customers tab and the third is the column to return. The fourth argument is the value to show when the key is absent, which is what keeps the row instead of producing an error.

Fill the formula down, then repeat it for each additional column you want, changing the third argument each time. A result of no match is a finding rather than a failure: it names the orders whose customer is missing from the second export.

XLOOKUP is the modern replacement for VLOOKUP here because it does not care which side of the lookup column the return column sits on, and the default value removes the need for IFERROR.

An Inner Join with COUNTIF

An inner join keeps only rows whose key appears on both sides. The simplest way to test that is to count the key in the other tab:

=FILTER(A2:B, COUNTIF(Customers!$A:$A, A2:A) > 0)

COUNTIF returns a count for every key in the Orders tab. FILTER keeps the rows where the count is above zero, so an order with no customer disappears. Use the same formula with the comparison reversed to list the rows that had no match:

=FILTER(A2:B, COUNTIF(Customers!$A:$A, A2:A) = 0)

That second formula is the anti-join, and it is often more useful than the join itself. It answers the question that a reconciliation report exists to answer: what is missing?

If you want the customer columns as well, add the XLOOKUP columns from the previous section first and filter on the whole block rather than on two columns.

A Full Outer Join with VSTACK and UNIQUE

A full outer join keeps every key from both tabs. Start by stacking both key columns and removing the duplicates:

=LET(keys, UNIQUE(VSTACK(Orders!B2:B, Customers!A2:A)), FILTER(keys, LEN(keys) > 0))

The FILTER removes the blank that the empty rows at the bottom of each range would otherwise contribute. The result is one list of every email that appears in either file.

Put that list in G1, then pull the order side and the customer side into the columns beside it:

=XLOOKUP(G2, Orders!$B:$B, Orders!$A:$A, "")

=XLOOKUP(G2, Customers!$A:$A, Customers!$B:$B, "")

Blank cells in the order column are customers who never ordered, and blank cells in the customer column are orders with no customer. A right join is the same construction with the tab order reversed.

This is the join that shows the whole picture. An inner join would have hidden both kinds of gap.

The Four Join Types at a Glance

The naming comes from database queries, where the same four operations are written as a single statement. The spreadsheet version spreads them across a few formulas, which makes each step visible and editable.

A Worked Example

The Orders tab holds two records:

The Customers tab holds two records with one shared key:

Each join type produces a different result from the same two files.

The inner join is the tidy answer. The full outer join is the honest one, because it shows that Bob exists without customer details and Carol exists without an order.

Duplicate and Mismatched Keys

An exact join assumes each key appears once on each side. Real exports do not always agree.

• Duplicate keys: XLOOKUP returns the first match it finds, so a repeated key silently hides the others. Count the key with COUNTIF before joining and decide which record is correct.
• Case and whitespace: Alice@Example.com and alice@example.com are different strings to a lookup. Standardise the key column with LOWER and TRIM before the join.
• Numbers stored as text: an order ID imported as text does not match the same ID stored as a number. Set both key columns to the same type, or convert one with VALUE.
• Near matches: Acme Ltd and Acme Limited are the same customer to a person and different keys to a formula. This is the case an exact join cannot solve.

The first three are preparation problems and a few helper columns fix them. The fourth needs fuzzy matching, which compares strings by similarity rather than equality and returns a score you can review.

Beyond Two Files

The formulas in this guide join two tabs on one exact key. That covers a large share of spreadsheet work, and it keeps the logic visible to anyone who opens the file.

The limit arrives when the keys are inconsistent or the join is only the first step in a longer clean-up. Flookup's fuzzy match scores near-duplicates, merge row combines records that belong together and the workflow templates bundle the sequence for vendor, order and user-list reconciliation.

When the files are too large for a spreadsheet, split them before the upload and join the parts that matter.

Joining Files Is a Key Problem

A join is one of the most common data tasks there is, and a spreadsheet already has the functions to perform it. Import both files, choose the key, and let XLOOKUP, COUNTIF or VSTACK carry the rows into one table.

Choose the join type by asking what should happen to a row with no match. If the answer matters, a full outer join shows it; if it does not, an inner join keeps the result small.

When the key itself is unreliable, Flookup Data Wrangler adds fuzzy matching and merge workflows beside the same data.

Frequently Asked Questions