How to join two spreadsheets on a common column
By the Analistable team · Updated · 4 min read
To join two spreadsheets, both need a column with the same values — an order ID, customer email or SKU. Clean that column (trim spaces, use one data type), then match rows with XLOOKUP for a quick lookup, Power Query's Merge for a repeatable join, or a SQL-style join when you need every match from both sides.
Part of our guide: How to join spreadsheets on a common column
Step 1: Pick and clean the key column
A join only works when the key column holds identical values in both files. Most failed matches come from invisible differences rather than missing data.
- Trim leading and trailing spaces (TRIM in Excel and Google Sheets).
- Store IDs as one type — a number 1042 does not match the text "1042".
- Normalise case for emails and codes (LOWER) before matching.
- Check for duplicates in the lookup table: one key should map to one row.
A worked example: customers and orders
We'll use two small tables we built for this guide. The Customers sheet has one row per customer; the Orders sheet has one row per order, so a customer can appear several times. The shared column is customer_id.
Notice two traps we planted on purpose: C-104 in Orders has a trailing space, and C-105 placed an order but doesn't exist in Customers. Both are the kind of thing that silently breaks real exports.
| customer_id | name | country |
|---|---|---|
| C-101 | Bakery Lune | France |
| C-102 | Nordic Bikes | Sweden |
| C-103 | Casa Verde | Spain |
| C-104 | Atlas Print | Malta |
| order_id | customer_id | amount_eur |
|---|---|---|
| O-1 | C-101 | 120 |
| O-2 | C-101 | 80 |
| O-3 | C-102 | 300 |
| O-4 | C-104 | 55 |
| O-5 | C-105 | 40 |
Method 1: XLOOKUP for a quick one-column lookup
When you only need to bring one or two columns from the second sheet into the first, XLOOKUP is the simplest option. In Excel 365 and Google Sheets: =XLOOKUP(A2, Customers!A:A, Customers!C:C, "Not found").
The fourth argument returns a readable value instead of #N/A when there's no match, which makes gaps easy to filter. The limitation: you need one formula per column, and it only returns the first match.
Run against our example, XLOOKUP from Orders into Customers gives the result below. O-4 fails only because of the trailing space — wrapping the lookup value in TRIM fixes it. O-5 is a genuine gap you'd want to investigate.
| order_id | customer_id | name |
|---|---|---|
| O-1 | C-101 | Bakery Lune |
| O-2 | C-101 | Bakery Lune |
| O-3 | C-102 | Nordic Bikes |
| O-4 | C-104 | Not found (trailing space) |
| O-5 | C-105 | Not found (missing customer) |
Method 2: Power Query Merge for repeatable joins
In Excel, Data → Get Data loads both tables into Power Query. Choose Merge Queries, select the key column in each table and pick a join kind: Left Outer keeps every row of the first table, Inner keeps only matching rows, Full Outer keeps everything.
Because the steps are saved, you can refresh the join when next month's export arrives instead of rebuilding formulas.
Method 3: A SQL-style join across files
When one customer has many orders, a lookup returns only the first order. A real join returns every matching pair, which is what you need for totals per customer, per region or per product.
Analistable runs this kind of join in your browser: connect or upload both sheets, confirm the suggested link between the key columns, and ask a question such as "total revenue per customer this quarter". The rows never leave your computer.
Here is "revenue per customer" on our cleaned example (spaces trimmed) using a left join from Customers to Orders. Every customer appears, including Casa Verde with no orders — something a lookup in the other direction would hide. The orphan order O-5 drops out, so check for it separately with an anti-join or a "Not found" filter.
| name | orders | revenue_eur |
|---|---|---|
| Bakery Lune | 2 | 200 |
| Nordic Bikes | 1 | 300 |
| Atlas Print | 1 | 55 |
| Casa Verde | 0 | 0 |
Which method should you use?
Use XLOOKUP for a one-off column. Use Power Query when the same files are joined every month in Excel. Use a SQL-style join when you have one-to-many relationships or need answers across more than two sheets.
Frequently asked questions
- Why does my lookup return #N/A when the value is clearly there?
- Usually a hidden difference: a trailing space, a number stored as text, or different capitalisation. Clean both key columns with TRIM and make the data types match.
- Can I join a Google Sheet with an Excel file?
- Yes. Download the Google Sheet as .xlsx and use Power Query, or connect both sources in Analistable and join them without converting anything.
- What is the difference between a left join and an inner join?
- A left join keeps every row from the first table and fills blanks where there's no match. An inner join keeps only rows that match in both tables.
- How do I merge two Excel spreadsheets based on a common field?
- Make sure the common field (an ID, email or SKU) is clean and the same data type in both files, then use XLOOKUP to bring columns across, or Power Query's Merge Queries to join the two tables on that field.