Excel for Data Analytics · Lesson 7
Connect transaction data with product master data, choose the right lookup approach, and validate matches before making business decisions.
Complete both the activity and MCQ for every concept. Finish with the explicit lesson-completion button.
DATA MATCHING · CONCEPT 1 OF 8
In real-world analytics, useful information is often spread across different tables. A sales table may contain a Product ID while a separate product table contains the product name, category and price. Lookup functions help connect these records.
Step 1 of 2 · Business matching
A sales table contains Product ID P003, but not the product name. Which task is a lookup function designed to perform?
Analyst Workflow
Identify the business question, determine the matching key, find the correct source table, return the required field, and validate the result before analysis.
What information is needed?
How are records matched?
Which table contains it?
Which field is required?
Can the result be trusted?
Final Analyst Challenge
Management needs category-level sales analysis. Connect each transaction’s Product ID to the product master, return the category, and check unmatched IDs before trusting the dashboard.
01
Product ID
02
Product Master
03
Category
04
Check unmatched IDs
Question → Key → Lookup → Validate → Analyze
Excel lookup functions connect related information across sales, customer, product master and other business tables. They are essential when analysts enrich transaction data before reporting.
XLOOKUP searches a lookup array and returns a corresponding value from another range. VLOOKUP searches the first column of a selected table and returns information from another column.
Reliable matching starts with a suitable lookup key such as Product ID, Customer ID or Employee ID. Check for missing, inconsistent or incorrectly entered keys.
Lookup functions enrich transaction data with attributes from master tables, making it easier to group, filter and analyze business information.