Why lookups connect related data in Excel - Dashboard Impact

Dashboard Mode - Why lookups connect related data

Business Question

How can we connect sales data with product details to see product names and prices alongside sales?

Sample Data

Order ID	Product ID	Quantity
1001	P01	3
1002	P03	5
1003	P02	2
1004	P04	1
1005	P01	4

Product ID	Product Name	Price
P01	Notebook	2.5
P02	Pen	1.2
P03	Eraser	0.5
P04	Ruler	1.0

Dashboard Components

• Sales Table with Product Details: Shows Order ID, Product ID, Quantity, Product Name, Price, and Total Price.
  Formula for Product Name (cell D2): =VLOOKUP(B2, $G$2:$I$5, 2, FALSE)
  Formula for Price (cell E2): =VLOOKUP(B2, $G$2:$I$5, 3, FALSE)
  Formula for Total Price (cell F2): =C2 * E2
• Total Sales: Sum of all Total Price values.
  Formula: =SUM(F2:F6)

Dashboard Layout

+-----------------------------+
| Sales Table with Product     |
| Details (Order, Product, Qty,|
| Name, Price, Total Price)    |
|                             |
| +-------------------------+ |
| | Order | Prod | Qty | ... | |
| +-------------------------+ |
+-----------------------------+
+-----------------------------+
| Total Sales: $XX.XX          |
+-----------------------------+

Interactivity

Adding a filter by Product ID or Product Name will update the Sales Table and Total Sales to show only matching orders and their totals.

Self Check

If you add a filter to show only Product ID = 'P01', which orders and totals will update in the Sales Table and Total Sales?

Key Result

Connect sales orders with product details using VLOOKUP to show product names, prices, and calculate total sales.