Merging queries (joins) in Excel - Step-by-Step Guide

Introduction

Merging queries lets you combine data from two tables based on matching values. This helps you see related information together without copying or typing it again.

When you have a list of customers and a separate list of their orders and want to see orders next to customer names.

When you want to combine product details with sales data to analyze performance.

When you have employee info in one table and their department info in another and want a full view.

When you want to add address details to a list of contacts from another table.

When you want to compare two lists and find matching or missing items.

Steps

Step 1: Click

- Data tab > Get & Transform Data group > Get Data > Combine Queries > Merge Queries

The Merge dialog box opens showing available tables to merge

Step 2: Select

- Top dropdown in Merge dialog

The first table you want to merge is chosen

Step 3: Select

- Bottom dropdown in Merge dialog

The second table you want to merge is chosen

Step 4: Click

- Column(s) in the first table

The column(s) to match on are highlighted

Step 5: Click

- Column(s) in the second table

The matching column(s) in the second table are highlighted

Step 6: Choose

- Join Kind dropdown

The type of join (e.g., Left Outer, Inner) is selected

Step 7: Click

- OK button in Merge dialog

A new query is created combining data from both tables based on the join

Step 8: Click

- Expand icon next to the merged column in the query editor

You see a list of columns from the second table to add to the first

💡 Uncheck columns you don't need to keep your data clean

Step 9: Click

- Close & Load button

The merged data loads into a new worksheet or table

Before vs After

Before

Table1 has 10 customers with IDs; Table2 has 15 orders with customer IDs but no customer names

After

Merged table shows 10 customers with their matching orders including customer names and order details

Settings Reference

Join Kind

📍 Merge dialog box

Controls which rows from each table appear in the merged result

Default: Left Outer

Expand Columns

📍 Query Editor after merge, next to merged column

Choose which columns from the second table to add to the merged data

Default: All columns selected

Common Mistakes

Selecting columns with different data types for matching

Excel cannot match values if one column is text and the other is number

Ensure both columns have the same data type before merging

Choosing the wrong join kind, like Inner join when you want all records

Inner join excludes unmatched rows, so some data is missing

Use Left Outer join to keep all rows from the first table

Not expanding the merged column to see the joined data

Merged data stays hidden inside a single column

Click the expand icon and select columns to display merged data

Summary

Merging queries combines two tables based on matching columns to show related data together.

Choose the right join kind to control which rows appear in the merged result.

Always expand the merged column to see and use the joined data.

Practice

1. What does merging queries (joins) in Excel usually help you do?

easy

A. Combine data from two tables based on a common value

B. Sort data alphabetically

C. Create charts from data

D. Format cells with colors

Solution

1. Step 1: Understand the purpose of merging queries

   Merging queries means joining data from two tables using a common value to combine related information.

2. Step 2: Identify what merging queries does in Excel

   It helps to combine and compare data easily without manual copying or sorting.

3. Final Answer:

   Combine data from two tables based on a common value -> Option A

4. Quick Check:

   Merging queries = Combine data [OK]

Hint: Merging joins tables by matching common values [OK]

Common Mistakes:

• Confusing merging with sorting
• Thinking it creates charts
• Assuming it changes cell colors

2. Which of these is the correct syntax to merge two tables using XLOOKUP in Excel?

easy

A. =XLOOKUP(A2, Table2[Value], Table2[ID])

B. =XLOOKUP(Table2[Value], A2, Table2[ID])

C. =XLOOKUP(Table2[ID], Table2[Value], A2)

D. =XLOOKUP(A2, Table2[ID], Table2[Value])

Solution

1. Step 1: Recall XLOOKUP syntax

   XLOOKUP(lookup_value, lookup_array, return_array) finds lookup_value in lookup_array and returns corresponding value from return_array.

2. Step 2: Match syntax to merging tables

   We want to find A2 (key) in Table2[ID] and return Table2[Value], so =XLOOKUP(A2, Table2[ID], Table2[Value]) is correct.

3. Final Answer:

   =XLOOKUP(A2, Table2[ID], Table2[Value]) -> Option D

4. Quick Check:

   XLOOKUP syntax = lookup_value, lookup_array, return_array [OK]

Hint: XLOOKUP: lookup_value, lookup_array, return_array order [OK]

Common Mistakes:

• Swapping lookup_array and return_array
• Using wrong column references
• Putting return_array before lookup_array

3. Given these tables:

Table1:
ID | Name
1 | Alice
2 | Bob

Table2:
ID | Score
1 | 85
3 | 90

If you use =XLOOKUP(A2, Table2[ID], Table2[Score], "Not Found") in Table1 where A2=1, what is the result?

medium

A. 85

B. 90

C. Not Found

D. 1

Solution

1. Step 1: Understand the lookup

   The formula looks for A2=1 in Table2[ID]. Table2 has ID 1 with Score 85.

2. Step 2: Return the matching Score

   Since ID 1 is found, XLOOKUP returns 85 from Table2[Score].

3. Final Answer:

   85 -> Option A

4. Quick Check:

   Lookup 1 returns 85 [OK]

Hint: XLOOKUP returns matching value or fallback if none [OK]

Common Mistakes:

• Choosing score for ID 3 instead of 1
• Confusing fallback value with result
• Returning ID instead of Score

4. You wrote this formula to merge data:
=XLOOKUP(A2, Table2[Value], Table2[ID])
But it returns wrong results. What is the likely error?

medium

A. Used wrong function name

B. Forgot to lock cell references

C. Swapped lookup_array and return_array columns

D. Used incorrect table name

Solution

1. Step 1: Check XLOOKUP parameters

   lookup_array should be the column with keys to find (e.g., IDs), return_array the column with values to return.

2. Step 2: Identify swapped columns

   Using Table2[Value] as lookup_array and Table2[ID] as return_array is reversed; it should be lookup ID, return Value.

3. Final Answer:

   Swapped lookup_array and return_array columns -> Option C

4. Quick Check:

   Lookup and return arrays must be in correct order [OK]

Hint: Lookup array must be key column, return array the value column [OK]

Common Mistakes:

• Mixing lookup and return columns
• Assuming function name is wrong
• Ignoring cell reference locking

5. You have two tables:

Employees:
ID | Name
101 | John
102 | Mary
103 | Steve

Salaries:
ID | Salary
101 | 5000
103 | 4500

You want to create a merged table showing all employees and their salaries, showing "No Data" if salary is missing. Which formula in cell C2 (next to Name) correctly merges salaries?

hard

A. =XLOOKUP(Name, Salaries[ID], Salaries[Salary], "No Data")

B. =XLOOKUP(A2, Salaries[ID], Salaries[Salary], "No Data")

C. =XLOOKUP(A2, Salaries[Salary], Salaries[ID], "No Data")

D. =XLOOKUP(A2, Employees[ID], Salaries[Salary], "No Data")

Solution

1. Step 1: Identify lookup value and arrays

   We want to find employee ID in Salaries[ID] and return Salaries[Salary]. If not found, show "No Data".

2. Step 2: Check formula correctness

   =XLOOKUP(A2, Salaries[ID], Salaries[Salary], "No Data") uses =XLOOKUP(A2, Salaries[ID], Salaries[Salary], "No Data"), which matches the requirement perfectly.

3. Final Answer:

   =XLOOKUP(A2, Salaries[ID], Salaries[Salary], "No Data") -> Option B

4. Quick Check:

   Lookup ID in Salaries, return Salary or fallback [OK]

Hint: Use XLOOKUP with fallback to handle missing data [OK]

Common Mistakes:

• Using wrong lookup or return columns
• Referencing Name instead of ID
• Mixing tables in lookup arrays