Why lookups connect datasets in Google Sheets - Business Case Study

Scenario Mode

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

📋 Request: Your manager wants you to combine sales data with product details to analyze sales by product category.

📊 Data: You have two datasets: one with sales transactions including product IDs and quantities sold, and another with product details including product IDs, names, and categories.

🎯 Deliverable: Create a combined table that shows each sale with the product name and category using lookup formulas.

Sample Data

Sale ID	Product ID	Quantity Sold
1001	P001	3
1002	P003	1
1003	P002	5
1004	P004	2
1005	P001	4

Product ID	Product Name	Category
P001	Notebook	Stationery
P002	Pen	Stationery
P003	Water Bottle	Accessories
P004	Backpack	Accessories

1

Step 1: Create a new table with columns: Sale ID, Product ID, Quantity Sold, Product Name, Category.

Set up headers in cells A1:E1 as: Sale ID, Product ID, Quantity Sold, Product Name, Category.

Expected Result

Headers appear correctly in the new table.

2

Step 2: Copy the sales data (Sale ID, Product ID, Quantity Sold) into the new table under the respective headers.

Copy values from the sales data table into A2:C6.

Expected Result

Sales data is correctly copied into the new table.

3

Step 3: Use VLOOKUP to find the Product Name for each Product ID in the sales table.

In cell D2, enter: =VLOOKUP(B2, $G$2:$I$5, 2, FALSE)

Expected Result

Cell D2 shows 'Notebook' for Product ID 'P001'.

4

Step 4: Copy the VLOOKUP formula down from D2 to D6 to fill Product Names for all sales.

Drag the formula in D2 down to D6.

Expected Result

Product Names appear correctly for all sales.

5

Step 5: Use VLOOKUP to find the Category for each Product ID in the sales table.

In cell E2, enter: =VLOOKUP(B2, $G$2:$I$5, 3, FALSE)

Expected Result

Cell E2 shows 'Stationery' for Product ID 'P001'.

6

Step 6: Copy the VLOOKUP formula down from E2 to E6 to fill Categories for all sales.

Drag the formula in E2 down to E6.

Expected Result

Categories appear correctly for all sales.

Final Result

Sale ID | Product ID | Quantity Sold | Product Name | Category
-------------------------------------------------------------
1001    | P001       | 3             | Notebook     | Stationery
1002    | P003       | 1             | Water Bottle | Accessories
1003    | P002       | 5             | Pen          | Stationery
1004    | P004       | 2             | Backpack     | Accessories
1005    | P001       | 4             | Notebook     | Stationery

✓Using lookup formulas connects sales data with product details easily.

✓This combined data helps analyze sales by product category.

✓For example, Stationery products sold more units overall than Accessories.

Bonus Challenge

Create a summary table that shows total quantity sold per product category.

Show Hint

Use SUMIF to add quantities based on the Category column.