How to join spreadsheets on a common column
By the Analistable team · Updated · 12 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.
XLOOKUP has a few abilities that cover most everyday joins:
- Several columns at once. If the lookup table is an Excel table,
=XLOOKUP([@[Customer ID]], Customers[Customer ID], Customers[[Name]:[Region]])returns Name and every column through to Region, spilling across the row. - Two-column keys. When a row is identified by store and date together, join the keys with a separator:
=XLOOKUP(A2&"|"&B2, Sales[Store]&"|"&Sales[Date], Sales[Amount]). The separator stops “1” + “23” matching “12” + “3”. - Nearest match. A match mode of -1 returns the next smaller value when there is no exact match, which is how you look up a price band or a tax rate effective from a date:
=XLOOKUP(D2, Rates[From], Rates[Rate], , -1). - Older versions. Excel 2019 and earlier have no XLOOKUP.
=INDEX(Customers!B:B, MATCH(B2, Customers!A:A, 0))does the same exact-match job, and the 0 is essential: without it MATCH assumes sorted data and can return a wrong row.
When you need every match rather than the first, use FILTER, which spills all matching rows: =FILTER(Orders[Order ID], Orders[Customer ID]=A2, "None"). To fit them into one cell, wrap it in TEXTJOIN; see TEXTJOIN to combine values from matched rows. If the lookup table lives in another file, read XLOOKUP from another workbook for how links behave when that file is closed.
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.
Some details of the Merge dialog are easy to miss. To join on two columns, Ctrl-click them in the same order in both tables; Power Query numbers them 1 and 2 so you can see the pairing. The dialog reports how many rows matched, for example “The selection matches 4,812 of 5,000 rows from the first table”, which is your first quality check before you click OK. Matching is case-sensitive and treats text and numbers as different types, so set both key columns to the same data type before merging. For keys that are spelled slightly differently, such as company names, tick Use fuzzy matching and set a similarity threshold; Power Query fuzzy merge explains the options and how to review its matches.
A third Excel option avoids flattening altogether: load both tables into the Data Model and create a relationship between them, then build a pivot table that uses fields from both. It suits large tables that you summarise rather than list. See Excel Data Model relationships.
Method 3: a SQL join (every match, any size)
SQL describes a join in one statement, which makes it easy to read back and repeat. Take four customers and five orders, where one order belongs to a customer ID (C-109) that isn't in the customer list:
| customers.customer_id | customers.name | orders.order_id | orders.customer_id | orders.amount |
|---|---|---|---|---|
| C-101 | Bakery Lune | O-9001 | C-101 | 120 |
| C-102 | Hart & Co | O-9002 | C-103 | 75.5 |
| C-103 | Nordic Supply | O-9003 | C-101 | 60 |
| C-104 | Okra Kitchen | O-9004 | C-103 | 210 |
| O-9005 | C-109 | 48 |
A left join that totals orders for every customer, including those with none:
SELECT c.customer_id, c.name,
COUNT(o.order_id) AS orders,
COALESCE(SUM(o.amount), 0) AS total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name
ORDER BY c.customer_id;| customer_id | name | orders | total |
|---|---|---|---|
| C-101 | Bakery Lune | 2 | 180 |
| C-102 | Hart & Co | 0 | 0 |
| C-103 | Nordic Supply | 2 | 285.5 |
| C-104 | Okra Kitchen | 0 | 0 |
COUNT(o.order_id) counts only matched orders, so customers without orders show 0 rather than 1. The orders total 465.5 in this result, but the orders table adds up to 513.5: the 48.0 order for C-109 has no customer, so a left join from customers can't show it. Reverse the question to find it, with an anti join:
SELECT o.*
FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;
-- returns O-9005, C-109, 48.0Analistable 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, SQL joins across multiple tables and joining two CSV files by a column.
The same join in pandas, with a built-in check
In Python, pandas can tell you which side every row came from and stop if the keys aren't what you expect:
import pandas as pd
customers = pd.read_excel("Customers.xlsx", dtype={"customer_id": str})
orders = pd.read_csv("Orders.csv", dtype={"customer_id": str})
merged = customers.merge(
orders, on="customer_id", how="outer",
indicator=True, # adds a _merge column: both / left_only / right_only
validate="one_to_many", # raises an error if a customer ID repeats
)
print(merged["_merge"].value_counts())On the sample data above this prints 4 rows marked both (the four matched orders), 2 left_only (C-102 and C-104, customers with no orders) and 1 right_only (the C-109 order): seven rows from four customers and five orders. Reading both key columns as text keeps IDs such as 00123 intact. See merging DataFrames in pandas, or merging data frames in R for the dplyr equivalent.
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.
A quick way to find the culprits in Excel is a helper column in the lookup table: =COUNTIF([Customer ID], [@[Customer ID]])>1 flags every key that appears more than once. In Google Sheets, =COUNTIF(A:A, A2)>1 filled down does the same. Decide for each duplicate whether it is a genuine second record (two price rows from different dates) or an export error, because the fix is different.
When duplicate keys multiply rows: a worked example
Here is what happens when the lookup table holds two rows for the same key. A price list keeps history, so product P-1 appears twice, and three order lines need a price:
| prices.sku | prices.price | prices.valid_from | order_lines.line_id | order_lines.sku | order_lines.qty |
|---|---|---|---|---|---|
| P-1 | 9.5 | 2025-01-01 | L1 | P-1 | 2 |
| P-1 | 10 | 2026-01-01 | L2 | P-1 | 1 |
| P-2 | 4 | 2025-01-01 | L3 | P-2 | 5 |
A plain left join of order lines to prices on SKU returns five rows, not three: L1 and L2 each match both P-1 prices. Summing qty × price over the result gives 78.5, when the correct value at current prices is 50.0 (2 × 10 + 1 × 10 + 5 × 4). Nothing errors and the inflated figure looks plausible, which is why row counts matter. XLOOKUP hides the same problem differently: it silently returns the first P-1 price it finds, which may be the old one.
The fix is to make the key unique before joining, either by filtering the price list to the latest row per SKU or by adding the date to the join condition:
SELECT l.line_id, l.sku, l.qty, p.price, l.qty * p.price AS value
FROM order_lines l
LEFT JOIN prices p
ON p.sku = l.sku
AND p.valid_from = (SELECT MAX(valid_from) FROM prices p2 WHERE p2.sku = l.sku)
ORDER BY l.line_id;
-- 3 rows: L1 20.0, L2 10.0, L3 20.0 (total 50.0)If each order should use the price that was valid on the order date instead, compare valid_from with the order date rather than taking the latest. In Excel the equivalent is XLOOKUP with match mode -1 over a price table sorted by date, or sorting the lookup table newest first so XLOOKUP's first match is the current price. In Power Query, sort and then use Remove Rows → Remove Duplicates on the key; add a buffering step (Table.Buffer) after the sort if the wrong row survives, because Power Query does not always keep sort order when removing duplicates.
Joining in Google Sheets
Google Sheets has XLOOKUP, VLOOKUP, INDEX/MATCH and FILTER, and they behave much as in Excel. Two differences matter for joins. First, to fill a lookup down a whole column in one formula, wrap it in ARRAYFORMULA or use a range as the search key: =ARRAYFORMULA(IF(A2:A="", , XLOOKUP(A2:A, Customers!A:A, Customers!B:B, "Not found"))). Second, the data you want to join often sits in another spreadsheet file, which you bring in with IMPORTRANGE first; the first time you use it you must click Allow access. See linking two Google Sheets and using several IMPORTRANGE calls.
Sheets has no Power Query, and its QUERY function works on one range at a time, so it can't join two tables by itself. The usual pattern is to add the looked-up columns with XLOOKUP first, then run QUERY over the widened range to group and total. QUERY with multiple ranges explains how to stack ranges for QUERY, which is a different job from joining them. For an overview of every Sheets method, read combining data in Google Sheets.
Which method should you use?
| Situation | Best method | Why |
|---|---|---|
| Add one or two columns to a list, once | XLOOKUP (INDEX/MATCH in Excel 2019) | Fastest to write; result sits next to your data |
| Same join every month on new exports | Power Query Merge | Click Refresh instead of rebuilding formulas |
| One customer, many orders; need every match | Power Query Merge, SQL or pandas | Lookups return only the first match |
| Find rows with no partner (missing, unmatched) | Anti join in Power Query or SQL | Returns the unmatched rows directly |
| Large tables you only summarise | Excel Data Model relationship | No flattened copy; pivot across both tables |
| Data in Google Sheets and Excel together | Export one side, or a tool that reads both | Neither app joins across the other natively |
| Names spelled slightly differently | Power Query fuzzy merge | Matches on similarity, with a threshold you set |
If you are joining more than two tables, chain the joins one at a time and check the row count after each step; a problem in the second join is far easier to find before the third is added. Combining data from multiple sources walks through a three-source example.
Troubleshooting joins
| Symptom | Likely cause | Fix |
|---|---|---|
| XLOOKUP returns #N/A for keys you can see in both tables | Trailing spaces, or a number on one side and text on the other | TRIM both keys; convert with VALUE or TEXT so the types agree |
| Power Query Merge matches 0 rows | Key columns have different data types (Text vs Whole Number) | Set both key columns to the same type before merging |
| Left join returns more rows than the first table | Duplicate keys in the second table | Count keys with COUNTIF; deduplicate or add a second key column |
| Totals are higher after the join | Rows multiplied by a one-to-many match before summing | Total each table first, then join the totals |
| Some emails don't match in Power Query but do with XLOOKUP | Power Query is case-sensitive; Excel lookups are not | Lowercase both columns (Transform → Format → lowercase) |
| IDs like 00123 match nothing | Leading zeros dropped when a CSV was opened in Excel | Import the column as Text, or pad with TEXT(A2, "00000") |
| Dates don't match | One side holds a date-time, the other a date only | Truncate both to a date (INT in Excel, Date.From in Power Query) |
How to check a join before you trust it
- Write down the row count of each table before joining, and the count you expect afterwards (a left join should equal the first table's count if the keys are unique).
- Count the unmatched rows on each side. In Excel,
=COUNTIF(D:D, "Not found")on the XLOOKUP column; in Power Query, the match message in the Merge dialog; in SQL, the anti join above. - Compare one total from each source with the joined result, as in the 465.5 versus 513.5 example: the gap should equal the unmatched rows exactly.
- Spot-check three keys by hand, including one that appears several times in the second table.
- Keep the unmatched rows in a separate sheet rather than discarding them; they are usually the most useful output of the whole exercise.
For a join you repeat, keep these checks in the workbook next to the result so a refresh that breaks the match rate is visible straight away. Reconciling data in Excel builds this into a monthly routine for finance data.
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
- Airtable vs spreadsheets for linked data
Airtable linked records against spreadsheet lookups: how each handles related tables, where spreadsheets still win and when it is time to move.
- Alteryx alternatives for occasional spreadsheet joins
Alteryx is excellent for complex, repeatable data workflows. For occasional spreadsheet joins, these lighter options may do the same job for less.
- Commission calculation template
Free sales commission template: looks up a rate per product line for each deal, totals commission per rep and adds an accelerator above quota.
- How to combine data from multiple sources
Bring CRM, billing, ads and spreadsheet data together: choose a common key, build a mapping table, align time and currency, then stack or join.
- 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 combine student grades and attendance records
Join class lists, grades and attendance by student ID to spot students at risk, find missing assignments and compare attendance with results.
- How to combine supplier price lists into one spreadsheet
Stack price lists from several suppliers into one table, map their codes to your SKUs and see the cheapest supplier for every product.
- How to compare event registrations with attendance
Match a registration list to check-ins or webinar attendance: attendance rate, no-shows to follow up, walk-ins to add, and which leads attended.
- How to do an inner join in Excel
Keep only rows that match in both tables. Do an inner join in Excel with Power Query Merge, or with one FILTER + XMATCH formula 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 fix mismatched IDs before joining two spreadsheets
IDs like CUST-0042, cust42 and 42 refer to the same record but never join. Normalise prefixes, padding, dashes and case with formulas, Power Query or SQL.
- How to join on dates and date ranges
Match each transaction date to the price, rate or fiscal period valid on that day with XLOOKUP, SUMIFS, FILTER or a SQL BETWEEN join.
- How to join on the closest match with an approximate lookup
Find the band, tier or rate for a value with approximate-match lookups in Excel and Sheets, what sorting they need, and ASOF joins in SQL.
- How to join tables with Excel's Data Model
Use Excel's Data Model to relate tables instead of VLOOKUP: create relationships, build a PivotTable across tables, and know the requirements and limits.
- How to join two lists on email addresses safely
Normalise case, whitespace and aliases before matching contact lists on email, avoid false matches, and keep personal data handling in check.
- 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 join two tables on two columns at once
Match rows on two or more columns with a helper key, XLOOKUP boolean arrays, a Power Query multi-column merge or a SQL composite key.
- How to match an employee list to training records
Check training compliance in Excel: join the HR employee list to training records, find who's missing a course, and list certifications expiring soon.
- How to match CRM deals to invoices
Find closed-won deals that were never invoiced and invoices with no deal: match CRM exports to your invoice list by deal ID, customer and amount.
- How to match Google Ads conversions to CRM deals
Check which Google Ads leads became revenue: store the GCLID in your CRM, join deals to campaigns, and compare reported conversions with closed revenue.
- How to match HubSpot contacts to Stripe customers
Join HubSpot contacts to Stripe customers by email: normalise addresses, handle duplicate Stripe records, find paying leads and payers missing from the CRM.
- How to match names that are spelled differently across sheets
Match company or customer names with typos and different spellings using normalisation, similarity scores and a review queue, without Power Query.
- How to match support tickets to customer accounts
Join a Zendesk or helpdesk ticket export to customer accounts by email domain to see ticket volume by plan, revenue and priority, and spot at-risk accounts.
- How to match survey responses to your customer list
Join survey answers to customer data by email to analyse results by plan, region or spend, calculate NPS per segment, and see who hasn't responded.
- How to match timesheet hours to projects and invoices
Roll up time logged in Jira or a timesheet tool by project, compare it with budgeted hours and with hours invoiced, and find unbilled work.
- 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 merge product data from several sources
Merge product catalogues and feeds from suppliers, your shop and your ERP on SKU or GTIN, and decide which source wins when attributes conflict.
- 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.
- How to use XLOOKUP with another workbook
Look up and link data from another Excel workbook with XLOOKUP and external references: syntax, closed files, updating links and fixing broken ones.
- How trailing and non-breaking spaces break matches between spreadsheets
Invisible trailing spaces and non-breaking spaces stop values matching across sheets. Detect them with LEN and CODE, then clean with TRIM and SUBSTITUTE.
- Lead to customer match template
Free Excel template that matches a lead list to a customer list by email, flags which leads converted and totals conversion rate and revenue by source.
- Left join vs XLOOKUP: when and why the results differ
A left join can return more rows than XLOOKUP, show blanks where XLOOKUP shows 0, and miss matches XLOOKUP finds. Why, and which behaviour you want.
- Numbers stored as text stop lookups working — how to fix it
A number and the same digits stored as text never match in VLOOKUP, XLOOKUP or a join. Spot text-numbers, convert them, or cast keys to one type.
- Project hours vs budget template
Free Excel template that totals timesheet hours and cost per project, compares them with budgeted hours and flags projects at risk or over budget.
- 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.
- SEO report template for GA4 and Search Console
Free SEO report workbook that joins Search Console page data with GA4 landing pages by URL path, showing CTR, engagement, key events and flags.
- Survey merge template
Free spreadsheet template that joins survey responses to your customer list by email, adds segment and region, and summarises scores by segment.
- VLOOKUP returns #N/A across sheets: every cause and its fix
A checklist of every reason VLOOKUP or XLOOKUP returns #N/A between sheets or files — ranges, match mode, types, spaces, missing keys — with a fix for each.
- VLOOKUP vs Power Query vs SQL: which should you use to join sheets?
Choose between VLOOKUP, Power Query and SQL to join spreadsheets by data size, how often you repeat the job and how comfortable you are with each.
- 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.
- Why a lookup fails on numbers with decimals
Exact-match lookups compare stored values, not what the cell shows. Hidden decimals and floating-point noise break numeric keys; ROUND or TEXT fixes them.
- Why you get duplicate rows after joining two tables
A join that returns more rows than you started with has a non-unique key. Find the duplicate keys, then dedupe or aggregate before you join.
- XLOOKUP returns the wrong row after merging — how to find and fix silent mismatches
XLOOKUP can return a value from the wrong row without any error: duplicate keys, approximate match, misaligned ranges and wildcards. How to catch each.
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.
- Why does my join return more rows than expected?
- The second table has duplicate keys, so each row in the first table matches several rows. Count each key in the second table with COUNTIF and remove or separate the duplicates before joining.
- Can I join on two columns?
- Yes. In Power Query, Ctrl-click both columns in each table in the same order. With XLOOKUP, join the keys with a separator, such as A2&"|"&B2, on both sides. In SQL, put both conditions in the ON clause.