Merge Two Tables (Add Columns by Key)

All versionsVLOOKUP

Bring columns from a second table into your first — a SQL-style join — by matching on a shared key. A VLOOKUP (or XLOOKUP) per added column pulls the related data in.

Quick formula: add the price from a product table, keyed on product ID:

=VLOOKUP(A2, products, 2, FALSE)

For each row in your main table, look the key up in the second table and return the column you want to add. Fill down.

Functions used (tap for the full reference guide):

VLOOKUP XLOOKUP

The example

Adding a price column to an orders list.

	A	B	C
1	Order	Product	Price (joined)
2	#1	P-10	$12.00

The formula

One lookup per column you’re adding:

=VLOOKUP(B2, productTable, 2, FALSE) // add price; FALSE = exact key

How it works

A lookup acts like a join:

Your main table has a key column (e.g. Product). The second table has that key plus the columns you want.
For each added column, VLOOKUP(key, secondTable, colIndex, FALSE) pulls the matching value.
Use XLOOKUP to return several columns at once: XLOOKUP(B2, ids, table[price]:table[stock]) spills them.
Wrap with IFERROR to flag keys that have no match in the second table.

Big joins? For thousands of rows or multi-column merges, Power Query’s Merge is faster and refreshable than a column of lookups. Formula lookups are perfect for live, lightweight joins right on the sheet.

Try it: interactive demo

Live demo

Pick a product; join its price.

Product

Joined price:

Variations

XLOOKUP multi-column

Add several columns at once:

=XLOOKUP(B2, ids, prods[price]:prods[stock])

Flag unmatched keys

No row in table 2:

=IFERROR(VLOOKUP(B2, t, 2, FALSE), "No match")

Power Query merge

Data → Get Data → Merge Queries for big joins.

Pitfalls & errors

Exact key (FALSE). Joins need an exact key match — approximate mode returns wrong rows.

Duplicate keys. VLOOKUP returns the first match; if the second table has duplicate keys, you only get one. De-dupe it first.

Convert to values when stable. A column of live lookups recalculates constantly; paste as values once the join is final for big datasets.

Practice workbook

📊

Download the free Merge Two Tables (Add Columns by Key) practice workbook

A two-table join with live VLOOKUP, the XLOOKUP-multi and flag-unmatched variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I merge two tables in Excel by a key?

Add a column with =VLOOKUP(key, secondTable, colIndex, FALSE) to pull related data into your main table — a formula-based join. Fill it down.

How do I add several columns from another table at once?

Use XLOOKUP with a multi-column return: =XLOOKUP(key, ids, table[col1]:table[col3]) spills all of them.

When should I use Power Query instead?

For large datasets or repeatable multi-column merges, Power Query's Merge Queries is faster and refreshable than a column of lookups.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Function references: VLOOKUP · XLOOKUP