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.

Practice

1. What is the main purpose of using a lookup function like VLOOKUP in Google Sheets?

easy

A. To sort data alphabetically

B. To find and retrieve matching data from another table

C. To add numbers in a column

D. To change the font style of cells

Solution

1. Step 1: Understand what lookup functions do

   Lookup functions like VLOOKUP search for a value in one table and return related information from another table.

2. Step 2: Compare options to the purpose

   Only To find and retrieve matching data from another table describes finding and retrieving matching data, which is what VLOOKUP does.

3. Final Answer:

   To find and retrieve matching data from another table -> Option B

4. Quick Check:

   Lookup = find matching data [OK]

Hint: Lookups find matching info across tables fast [OK]

Common Mistakes:

• Thinking lookup sorts data
• Confusing lookup with math functions
• Assuming lookup changes cell formatting

2. Which of the following is the correct syntax for VLOOKUP in Google Sheets?

easy

A. =VLOOKUP(range, search_key, index, [is_sorted])

B. =VLOOKUP(search_key, index, range, [is_sorted])

C. =VLOOKUP(index, search_key, range, [is_sorted])

D. =VLOOKUP(search_key, range, index, [is_sorted])

Solution

1. Step 1: Recall VLOOKUP syntax

   The correct order is search_key, range, index, and optional is_sorted.

2. Step 2: Match options to syntax

   Only =VLOOKUP(search_key, range, index, [is_sorted]) matches this order exactly.

3. Final Answer:

   =VLOOKUP(search_key, range, index, [is_sorted]) -> Option D

4. Quick Check:

   Syntax order = search_key, range, index [OK]

Hint: Remember: search_key first, then range [OK]

Common Mistakes:

• Swapping search_key and range
• Putting index before range
• Forgetting optional is_sorted argument

3. Given the table below in range A2:B5 where column A has product IDs and column B has prices:

A2: 101  B2: 5.00
A3: 102  B3: 7.50
A4: 103  B4: 6.25
A5: 104  B5: 8.00

If cell D2 contains 102, what will the formula =VLOOKUP(D2, A2:B5, 2, FALSE) return?

medium

A. Error

B. 102

C. 7.50

D. 6.25

Solution

1. Step 1: Identify the search_key and range

   The search_key is 102 from D2, and the range is A2:B5.

2. Step 2: Find 102 in column A and return column 2 value

   102 is in A3, so return B3 which is 7.50.

3. Final Answer:

   7.50 -> Option C

4. Quick Check:

   Lookup 102 price = 7.50 [OK]

Hint: Match search_key in first column, return value from index column [OK]

Common Mistakes:

• Returning the search_key instead of the price
• Confusing column index number
• Using TRUE instead of FALSE causing wrong match

4. A user wrote the formula =VLOOKUP(A2, B2:C10, 3, FALSE) but it returns an error. What is the most likely reason?

medium

A. The column index 3 is outside the range B2:C10

B. The search key A2 is missing

C. The range B2:C10 is not sorted

D. The FALSE argument is invalid

Solution

1. Step 1: Check the range columns

   Range B2:C10 has only 2 columns (B and C).

2. Step 2: Check the column index

   Column index 3 asks for a third column, which does not exist in the range.

3. Final Answer:

   The column index 3 is outside the range B2:C10 -> Option A

4. Quick Check:

   Column index must be ≤ number of columns [OK]

Hint: Column index cannot exceed range width [OK]

Common Mistakes:

• Assuming FALSE causes error
• Thinking range must be sorted with FALSE
• Ignoring column count in range

5. You have two tables: one with employee IDs and names (A2:B6), and another with employee IDs and sales numbers (D2:E6). How can you create a new column next to the names that shows each employee's sales using VLOOKUP?

hard

A. Use =VLOOKUP(A2, D2:E6, 2, FALSE) next to each name

B. Use =VLOOKUP(B2, A2:B6, 1, TRUE) next to each name

C. Use =VLOOKUP(E2, A2:B6, 2, FALSE) next to each name

D. Use =VLOOKUP(D2, A2:B6, 2, TRUE) next to each name

Solution

1. Step 1: Identify the lookup key and range

   The employee ID in A2 is the key to find sales in D2:E6.

2. Step 2: Use VLOOKUP with correct parameters

   Formula =VLOOKUP(A2, D2:E6, 2, FALSE) looks up ID in D2:E6 and returns sales from column 2.

3. Final Answer:

   Use =VLOOKUP(A2, D2:E6, 2, FALSE) next to each name -> Option A

4. Quick Check:

   Lookup ID in sales table to get sales [OK]

Hint: Lookup ID in sales table, return sales column [OK]

Common Mistakes:

• Using name instead of ID as search key
• Wrong range or column index
• Using TRUE causing approximate match errors