Why lookups connect related data in Excel - Business Case Study

Scenario Mode

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

📋 Request: Your manager wants a report that shows each sales order with the customer's name and region included.

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

🎯 Deliverable: Create a combined table that shows Order ID, Customer Name, Region, and Sales Amount for each order.

Sample Data

Order ID	Customer ID	Sales Amount
1001	C001	250
1002	C002	450
1003	C003	300
1004	C001	150
1005	C004	500

Customer ID	Customer Name	Region
C001	Alice	North
C002	Bob	South
C003	Charlie	East
C004	Diana	West

1

Step 1: Create a new table with columns: Order ID, Customer Name, Region, Sales Amount.

Set up headers in cells E1:H1 as 'Order ID', 'Customer Name', 'Region', 'Sales Amount'.

Expected Result

Headers appear in E1:H1.

2

Step 2: Copy Order ID from the sales orders table to the new table.

In cell E2, enter =A2 and drag down to E6.

Expected Result

Order IDs 1001, 1002, 1003, 1004, 1005 appear in E2:E6.

3

Step 3: Use VLOOKUP to find Customer Name for each order using Customer ID.

In F2, enter =VLOOKUP(B2,$F$10:$H$13,2,FALSE) and drag down to F6.

Expected Result

Customer names Alice, Bob, Charlie, Alice, Diana appear in F2:F6.

4

Step 4: Use VLOOKUP to find Region for each order using Customer ID.

In G2, enter =VLOOKUP(B2,$F$10:$H$13,3,FALSE) and drag down to G6.

Expected Result

Regions North, South, East, North, West appear in G2:G6.

5

Step 5: Copy Sales Amount from the sales orders table to the new table.

In H2, enter =C2 and drag down to H6.

Expected Result

Sales amounts 250, 450, 300, 150, 500 appear in H2:H6.

Final Result

Order ID | Customer Name | Region | Sales Amount
----------------------------------------------
1001     | Alice         | North  | 250
1002     | Bob           | South  | 450
1003     | Charlie       | East   | 300
1004     | Alice         | North  | 150
1005     | Diana         | West   | 500

✓VLOOKUP connects sales orders to customer details by matching Customer ID.

✓This combined table helps see who bought what and from which region.

✓Lookups make it easy to join related data from different tables.

Bonus Challenge

Create a total sales amount by region summary using SUMIF.

Show Hint

Use =SUMIF(range_of_regions, region_name, range_of_sales_amounts) to sum sales per region.