Analistable

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.

Two tables that share a Customer ID
Customers.xlsxOrders.csv
Customer IDNameOrder IDCustomer ID
C-101Bakery LuneO-9001C-101
C-102Hart & CoO-9002C-103
C-103Nordic SupplyO-9003C-101

The four join types in plain English

Join types and what they return
JoinKeepsTypical question
InnerOnly rows with a match in both tablesWhich customers have ordered?
LeftEvery row of the first table, matches where foundAdd order totals to my customer list
Full outerEvery row from both tablesReconcile two lists completely
Anti (left only)Rows of the first table with no matchWhich 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)

  1. Load both tables into Power Query (Data → From Table/Range, or From File for other workbooks).
  2. Select the first query and choose Home → Merge Queries.
  3. Click the key column in each table, choose the join kind and click OK.
  4. 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:

Sample data: customers and orders
customers.customer_idcustomers.nameorders.order_idorders.customer_idorders.amount
C-101Bakery LuneO-9001C-101120
C-102Hart & CoO-9002C-10375.5
C-103Nordic SupplyO-9003C-10160
C-104Okra KitchenO-9004C-103210
O-9005C-10948

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;
Result of the left join
customer_idnameorderstotal
C-101Bakery Lune2180
C-102Hart & Co00
C-103Nordic Supply2285.5
C-104Okra Kitchen00

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.0

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, 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:

Price list with history (prices) and order lines
prices.skuprices.priceprices.valid_fromorder_lines.line_idorder_lines.skuorder_lines.qty
P-19.52025-01-01L1P-12
P-1102026-01-01L2P-11
P-242025-01-01L3P-25

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?

Choosing a join method
SituationBest methodWhy
Add one or two columns to a list, onceXLOOKUP (INDEX/MATCH in Excel 2019)Fastest to write; result sits next to your data
Same join every month on new exportsPower Query MergeClick Refresh instead of rebuilding formulas
One customer, many orders; need every matchPower Query Merge, SQL or pandasLookups return only the first match
Find rows with no partner (missing, unmatched)Anti join in Power Query or SQLReturns the unmatched rows directly
Large tables you only summariseExcel Data Model relationshipNo flattened copy; pivot across both tables
Data in Google Sheets and Excel togetherExport one side, or a tool that reads bothNeither app joins across the other natively
Names spelled slightly differentlyPower Query fuzzy mergeMatches 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

Symptoms, causes and fixes
SymptomLikely causeFix
XLOOKUP returns #N/A for keys you can see in both tablesTrailing spaces, or a number on one side and text on the otherTRIM both keys; convert with VALUE or TEXT so the types agree
Power Query Merge matches 0 rowsKey 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 tableDuplicate keys in the second tableCount keys with COUNTIF; deduplicate or add a second key column
Totals are higher after the joinRows multiplied by a one-to-many match before summingTotal each table first, then join the totals
Some emails don't match in Power Query but do with XLOOKUPPower Query is case-sensitive; Excel lookups are notLowercase both columns (Transform → Format → lowercase)
IDs like 00123 match nothingLeading zeros dropped when a CSV was opened in ExcelImport the column as Text, or pad with TEXT(A2, "00000")
Dates don't matchOne side holds a date-time, the other a date onlyTruncate both to a date (INT in Excel, Date.From in Power Query)

How to check a join before you trust it

  1. 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).
  2. 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.
  3. 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.
  4. Spot-check three keys by hand, including one that appears several times in the second table.
  5. 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

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.