How to Merge Two Sheets in Excel by a Common Column
Join two tables on a shared key — pull the columns you need from a second sheet into your data — with Power Query, formulas, or one click.
Last updated: August 2026
| Your table: ID, Order | + from Sheet2 | |
|---|---|---|
| 1001, £120 | → | Acme Corp, Bahrain |
| 1002, £80 | → | Beta LLC, UAE |
Method 1 — Power Query Merge (built-in)
- Turn each table into a query: click inside it → Data → From Table/Range.
- Go to Data → Get Data → Combine Queries → Merge.
- Pick the shared key column in both queries and a join type (Left Outer keeps all your rows).
- Expand the merged column to choose which fields to bring in, then Close & Load.
Method 2 — VLOOKUP / XLOOKUP per column
Add one lookup formula for each column you want to pull over, e.g. =XLOOKUP(A2, Sheet2!A:A, Sheet2!B:B). Simple for one or two columns; tedious and fragile for many.
The 1-click way — Tellsheet's Merge by Key
Click Merge by Key, pick the second table, and match the shared key by column name. Tellsheet pulls the matching columns into your table — matching ignores case and spaces, so fewer rows come up blank — with a preview first. Need to stack sheets (add rows) instead of join them? Use Combine Sheets.
Frequently asked questions
How do I combine two sheets on a common column?
Use Power Query's Merge, a lookup formula per column, or Tellsheet's Merge by Key, which pulls the matching columns from a second table in one step.
What's the difference between merging and appending?
Merging joins tables side by side on a key (adds columns). Appending stacks them (adds rows) — for that use Tellsheet's Combine Sheets.
Why don't some rows match when I merge?
The key values differ — spaces, text-vs-number, or capitalization. Tellsheet matches ignoring case and spaces, and Match Doctor pinpoints and fixes keys that fail.
Related Excel guides
Merge sheets without the formulas
Get Tellsheet and join tables, look up values, and combine sheets — with a preview before every change and nothing leaving your PC.
Get Tellsheet free See pricing