How to join spreadsheets on a common column
By the Analistable team · Updated · 3 min read
To join two spreadsheets, both need a key column with the same values, such as an order ID or email. Clean the key (trim spaces, one data type), then use XLOOKUP to pull a few columns across, Power Query → Merge Queries for a repeatable join with a choice of join type, or a SQL join when you need every match from both sides.
Join vs stack: which do you need?
Stacking puts rows from similar files under each other. Joining puts columns from different tables next to each other, matched by a key. If your files have different columns describing the same customers, orders or products, you need a join. If they have the same columns, read combining sheets into one instead.
| Customers.xlsx | Orders.csv | ||
|---|---|---|---|
| Customer ID | Name | Order ID | Customer ID |
| C-101 | Bakery Lune | O-9001 | C-101 |
| C-102 | Hart & Co | O-9002 | C-103 |
| C-103 | Nordic Supply | O-9003 | C-101 |
The four join types in plain English
| Join | Keeps | Typical question |
|---|---|---|
| Inner | Only rows with a match in both tables | Which customers have ordered? |
| Left | Every row of the first table, matches where found | Add order totals to my customer list |
| Full outer | Every row from both tables | Reconcile two lists completely |
| Anti (left only) | Rows of the first table with no match | Which customers have never ordered? |
Read inner join in Excel for worked examples, and Power Query's six join kinds for the full list.
Method 1: XLOOKUP (quick, one value per row)
=XLOOKUP(B2, Customers!A:A, Customers!B:B, "Not found") returns the customer name for the ID in B2. It is the fastest way to add a column or two, and it works in Microsoft 365, Excel 2021 and Google Sheets. It returns the first match only, so it can't list every order for a customer. Compare it with VLOOKUP and INDEX MATCH in VLOOKUP vs XLOOKUP vs INDEX MATCH.
Method 2: Power Query Merge (repeatable, any join type)
- Load both tables into Power Query (Data → From Table/Range, or From File for other workbooks).
- Select the first query and choose Home → Merge Queries.
- Click the key column in each table, choose the join kind and click OK.
- Expand the new column to pick which fields to bring across, then Close & Load.
Unlike a lookup, a merge returns every match: a customer with three orders becomes three rows. Step-by-step: joining two tables in Excel.
Method 3: a SQL join (every match, any size)
In SQL, the same join is one statement:
SELECT c.name, o.order_id FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id
Analistable writes this kind of query for you: connect the sheets, confirm the shared column, and ask “which customers haven't ordered this quarter?”. If you write code yourself, see merging data in pandas, R and SQL.
Clean the key column first
- Remove leading and trailing spaces with TRIM.
- Use one data type: the number 1042 doesn't match the text "1042".
- Make case consistent for emails and codes with LOWER or UPPER.
- Check for duplicate keys in the lookup table — they multiply rows in a join.
Count rows before and after a join. If a left join returns more rows than your first table had, the lookup table has duplicate keys.
Joining marketing data
Search Console, GA4 and ad platforms all export spreadsheets that share a landing page or campaign column. Joining them answers questions none of them answers alone. See combining Google Analytics and Search Console data, merging GA4 with ad cost data and combining several GA4 properties into one report.
Every guide in this topic
- How to combine GA4 properties into one report
Combine GA4 properties into one report: roll-up properties (Analytics 360), Looker Studio, or stacking exports in a sheet — and which metrics add up.
- How to combine Google Analytics and Search Console data
Join GA4 landing page data with Search Console clicks and queries: the built-in link, matching URLs correctly, and estimating conversions per search query.
- How to do an inner join in Excel
Do an inner join in Excel — keep only rows that match in both tables — with Power Query Merge, or with FILTER, XMATCH and XLOOKUP in Microsoft 365.
- How to find pages losing clicks, and which ones matter
Compare two periods in Search Console to find pages losing clicks, then join GA4 conversions to fix the ones that matter most first. Formulas included.
- How to join two spreadsheets on a common column
Three reliable ways to join two spreadsheets by a shared ID or email: XLOOKUP, Power Query merge and a SQL-style join — plus the mistakes that break matches.
- How to join two tables in Excel
Join two Excel tables on a shared column with Power Query Merge Queries or XLOOKUP: step-by-step, choosing a join kind, and checking the result.
- How to merge GA4 data with ad cost data
Combine GA4 conversions and revenue with Google Ads, Meta or LinkedIn cost data by date and campaign to calculate ROAS and cost per conversion in a sheet.
- How to return every match in one cell with TEXTJOIN
XLOOKUP returns only the first match. Use TEXTJOIN with FILTER to list every matching value in one cell, in Excel and Google Sheets, with older-version options.
- Reusable lookups with LET and LAMBDA
Make long lookup and join formulas readable with LET, then turn them into your own reusable Excel function with LAMBDA and the Name Manager.
- VLOOKUP vs XLOOKUP vs INDEX MATCH: which should you use?
A clear comparison of VLOOKUP, XLOOKUP and INDEX MATCH: syntax, limitations, compatibility, and when a lookup isn't enough and you need a join.
Frequently asked questions
- How do I join two Excel sheets by a common column?
- Use XLOOKUP to bring columns from one sheet into the other, or Power Query's Merge Queries to join them as tables. Both match rows on the column the sheets share, such as an ID or email.
- What is the difference between a lookup and a join?
- A lookup returns one value per row (the first match). A join returns every matching row, so one customer with three orders gives three rows.
- Can I join an Excel file and a Google Sheet?
- Yes. Export the Google Sheet as .xlsx or CSV and join both in Power Query, or connect both in Analistable and join them without exporting.
- Why does my join return fewer rows than expected?
- Usually because keys don't match exactly: spaces, different capitalisation, or numbers stored as text. Clean both key columns and try again.