Why lookups connect datasets in Google Sheets - Dashboard Impact

Dashboard Mode - Why lookups connect datasets

Dashboard Goal

Understand how lookup formulas connect two datasets to show combined information in one place.

Sample Data

Product ID	Product Name	Category
101	Notebook	Stationery
102	Pen	Stationery
103	Water Bottle	Accessories
104	Backpack	Accessories
105	Desk Lamp	Electronics

Order ID	Product ID	Quantity
5001	101	2
5002	103	1
5003	105	4
5004	102	5
5005	104	3

Dashboard Components

• Order Details Table: Shows Order ID, Product ID, Quantity, Product Name, and Category using lookups.
  Formula for Product Name in cell D2:
  =VLOOKUP(B2, $A$2:$C$6, 2, FALSE)
  Formula for Category in cell E2:
  =VLOOKUP(B2, $A$2:$C$6, 3, FALSE)
  These find the Product Name and Category from the Product list matching the Product ID in the order.
• Total Quantity Sold per Category: Calculates total quantity sold for each product category.
  Formula example for Stationery category:
  =SUMIF(E$2:E$6, "Stationery", C$2:C$6)
  Where E2:E6 is the looked up Category column from Product list and C2:C6 is Quantity from Orders matched with categories.
• KPI Card - Total Orders: Counts total orders.
  Formula:
  =COUNTA(A2:A6)
• KPI Card - Total Quantity Sold: Sums all quantities.
  Formula:
  =SUM(C2:C6)

Dashboard Layout

+----------------------+-----------------------+
|      KPI Cards       |     Order Details      |
| +------------------+ | +-------------------+ |
| | Total Orders     | | | Order ID | Product | |
| | Total Quantity   | | | Product ID | Name  | |
| +------------------+ | | Quantity |       | |
|                      | +-------------------+ |
+----------------------+-----------------------+
|       Category Sales Summary (Table)           |
| +--------------------------------------------+ |
| | Category   | Total Quantity Sold            | |
| +--------------------------------------------+ |
+------------------------------------------------+

Interactivity

Add a filter dropdown for Category. When you select a category, the Order Details Table and Category Sales Summary update to show only orders and totals for that category. The KPI cards update to reflect counts and sums for the filtered data.

Self Check

If you add a filter to show only Accessories category, which orders appear in the Order Details Table? What is the total quantity sold for Accessories?

Key Result

This dashboard shows how lookup formulas connect product and order data to display combined order details and sales summaries by category.

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