Merging queries (joins) in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants a combined report showing sales details along with customer information for each transaction.

📊 Data: You have two tables: one with sales transactions including Customer ID, Product, and Amount; another with customer details including Customer ID, Name, and Region.

🎯 Deliverable: Create a merged table that joins sales data with customer details by Customer ID, showing Customer Name, Region, Product, and Amount.

Sample Data

Sales Data				Customer Data
Transaction ID	Customer ID	Product	Amount	Customer ID	Name	Region
1001	C001	Shirt	25	C001	Alice	North
1002	C002	Jeans	40	C002	Bob	East
1003	C003	Hat	15	C003	Charlie	South
1004	C001	Socks	5	C004	Diana	West
1005	C004	Shoes	60

1

Step 1: Create a new sheet named 'Merged Report' to hold the combined data.

Expected Result

A blank sheet named 'Merged Report' is ready.

2

Step 2: In 'Merged Report', set headers in row 1: Customer Name, Region, Product, Amount.

Expected Result

Headers appear in cells A1:D1.

3

Step 3: In cell A2 of 'Merged Report', enter formula to get Customer Name by matching Customer ID from Sales Data.

=IFERROR(VLOOKUP(SalesData!B2, CustomerData!$A$2:$C$5, 2, FALSE), "Unknown")

Expected Result

Customer Name appears for each Customer ID; 'Unknown' if no match.

4

Step 4: In cell B2, get Region from Customer Data using Customer ID from Sales Data.

=IFERROR(VLOOKUP(SalesData!B2, CustomerData!$A$2:$C$5, 3, FALSE), "Unknown")

Expected Result

Region appears for each Customer ID; 'Unknown' if no match.

5

Step 5: Copy Product from Sales Data to cell C2.

=SalesData!C2

Expected Result

Product name appears matching the sales record.

6

Step 6: Copy Amount from Sales Data to cell D2.

=SalesData!D2

Expected Result

Amount appears matching the sales record.

7

Step 7: Drag formulas in A2:D2 down to cover all sales rows (5 rows).

Expected Result

All sales rows show merged customer and sales info.

Final Result

Merged Report

| Customer Name | Region | Product | Amount |
|---------------|--------|---------|--------|
| Alice         | North  | Shirt   | 25     |
| Bob           | East   | Jeans   | 40     |
| Charlie       | South  | Hat     | 15     |
| Alice         | North  | Socks   | 5      |
| Diana         | West   | Shoes   | 60     |

✓Most customers have matching sales records.

✓Customer Diana's sales appear even though her Customer ID was missing in sales data for one transaction.

✓The join helps see customer region alongside their purchases.

Bonus Challenge

Create a summary table showing total sales amount by Region using the merged data.

Show Hint

Use SUMIF or a Pivot Table on the 'Region' column to sum 'Amount'.