How to do an inner join in Excel
By the Analistable team · Updated · 5 min read
An inner join keeps only the rows whose key appears in both tables. In Excel, use Data → Get Data → Combine Queries → Merge, select the key in both tables and choose Inner (only matching rows). In Microsoft 365 you can also do it with a formula: FILTER the first table to keys found by XMATCH in the second, then add the second table's columns with XLOOKUP.
Part of our guide: How to join spreadsheets on a common column
What an inner join returns
| Order ID | Customer ID | Amount | Name |
|---|---|---|---|
| O-9001 | C-101 | 120 | Bakery Lune |
| O-9002 | C-103 | 75 | Nordic Supply |
| O-9003 | C-101 | 80 | Bakery Lune |
Order O-9004 (customer C-104, not in Customers) and customer C-102 (no orders) are both left out. Use a left join instead when you must keep every order — compare the result in joining two tables in Excel.
Power Query
- Load both tables as connections (Data → From Table/Range → Close & Load To → Only Create Connection).
- Data → Get Data → Combine Queries → Merge.
- Choose the tables, click the key column in each, and set Join Kind to Inner (only matching rows).
- Expand the columns you need from the second table and Close & Load.
If a key appears twice in the second table, the order appears twice in the result — that's correct join behaviour, but check whether duplicates are expected.
A formula inner join (Microsoft 365)
=LET(
keys, Orders[Customer ID],
hit, ISNUMBER(XMATCH(keys, Customers[Customer ID])),
matched, FILTER(Orders, hit),
HSTACK(matched, XLOOKUP(FILTER(keys, hit), Customers[Customer ID], Customers[Name]))
)hit is TRUE for each order whose customer exists. FILTER keeps those orders, and XLOOKUP adds the matching name. This handles many-to-one joins (many orders per customer); for many-to-many, use Power Query.
Inner join vs other join kinds
- Left outer: all orders, customer fields blank where missing.
- Inner: only orders with a known customer.
- Left anti: only orders *without* a known customer — useful for data-quality checks.
All six join kinds are explained in Power Query Merge Queries: every join kind.
Checking an inner join
Compare the row count with the left join: the difference is the number of rows that had no match. If that number is surprising, run a left anti join (or =COUNTIF(Customers[Customer ID], B2)=0 next to each order) to list the unmatched keys and look for spaces, case differences or IDs stored as numbers in one table and text in the other.
| Check | Rows | Meaning |
|---|---|---|
| Orders | 4 | Starting table |
| Left outer join | 4 | One row per order, as expected |
| Inner join | 3 | Orders with a known customer |
| Left anti join | 1 | 4 − 3 = 1 order (O-9004) without a customer |
Inner join on two key columns
Sometimes a row is identified by two columns, such as Store and SKU. In Power Query's Merge dialog, hold Ctrl and click Store then SKU in the first table, and the same two columns in the same order in the second table; the small numbers 1 and 2 on the headers show the pairing. Choose Inner as before.
In a Microsoft 365 formula, build a combined key on the fly:
=LET(
k1, Sales[Store] & "|" & Sales[SKU],
k2, Stock[Store] & "|" & Stock[SKU],
hit, ISNUMBER(XMATCH(k1, k2)),
FILTER(Sales, hit, "No matches")
)The | separator stops "AB" & "1" and "A" & "B1" producing the same key. The third FILTER argument shows a message instead of #CALC! when nothing matches.
Inner join in Google Sheets
Google Sheets has no Merge Queries, but FILTER and XLOOKUP behave the same way. Using COUNTIF as the match test, which also works in older sheets:
=FILTER(Orders!A2:C, COUNTIF(Customers!A2:A, Orders!B2:B) > 0)Then add the customer name next to the result with =XLOOKUP(B2, Customers!A:A, Customers!B:B). COUNTIF ignores case, so c-101 and C-101 count as a match here, while Power Query merges are case-sensitive — worth knowing when results differ between tools. More options are in combining data in Google Sheets.
Excel 2019 and earlier
Without FILTER, add a helper column to Orders that marks matches, then filter on it:
=IF(COUNTIF(Customers[Customer ID], [@[Customer ID]]) > 0, "Match", "No match")Filter the helper column to Match and copy the visible rows, or use Power Query, which is built into Excel 2016 and 2019 for Windows. A COUNTIF helper only proves the key exists; bring other columns across with INDEX/MATCH.
Common inner join mistakes
- Using an inner join to enrich a sales report. Revenue for orders with a missing customer disappears from the total. Use a left join for reporting and an inner join only when unmatched rows truly should not count.
- Not noticing many-to-many keys. If both tables repeat a key, every combination is returned — two rows in each table produce four. Check for duplicates in the second table first.
- Assuming the result is ordered. Power Query does not promise to keep the original row order after a merge; sort explicitly before loading.
- Forgetting to refresh. A Power Query inner join is a snapshot until you click Data → Refresh All; a FILTER formula recalculates as soon as a customer is added, so the two can disagree for a while.
- Joining on names. "Bakery Lune" and "Bakery Lune Ltd" won't match. Use an ID, or try a fuzzy merge and review its matches by hand.
Frequently asked questions
- Can Excel do an inner join?
- Yes. Power Query's Merge Queries has an Inner join kind, and Microsoft 365 formulas (FILTER, XMATCH, XLOOKUP) can produce the same result.
- What is the difference between an inner join and a left join?
- An inner join keeps only rows that match in both tables. A left join keeps every row of the first table and fills blanks where there is no match.
- Does VLOOKUP do an inner join?
- Not by itself. VLOOKUP returns #N/A for missing matches; filtering those rows out afterwards gives an inner-join result for one-to-one data.
- How do I find the rows an inner join dropped?
- Run a left anti join in Power Query, or filter a COUNTIF helper column for zero matches. The count should equal the left-join rows minus the inner-join rows.