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.