Day Goal
Learn how to connect data across tables and add business logic using Excel’s most practical functions: XLOOKUP, IF, and TEXT.
Introduction
In real-world analysis, data rarely lives in just one table. Customer info might be in one file, while sales live in another.
Today, you’ll learn how analysts merge data intelligently and add meaning to numbers—turning raw data into insights.
This is a huge milestone in Excel mastery
Core Concepts
1️⃣ XLOOKUP – Find & Merge Data
What it does:
Looks for a value in one table and pulls matching data from another.
Why analysts love it:
-
Replaces VLOOKUP/HLOOKUP
-
Works left → right or right → left
-
Handles missing values cleanly
Example use case:
Match Customer ID in the Sales table to get the Customer Name from the Customer table.
Basic syntax:
=XLOOKUP(lookup_value, lookup_array, return_array)
2️⃣ IF – Apply Business Logic
What it does:
Checks a condition and returns different results depending on TRUE or FALSE.
Example:
Flag customers as High Value if total sales ≥ 50,000.
Basic syntax:
=IF(condition, value_if_true, value_if_false)
3️⃣ TEXT – Control How Data Looks
What it does:
Formats numbers or dates as text.
Example uses:
-
Convert dates to “Jan 2026”
-
Format currency as
₱50,000
Basic syntax:
=TEXT(value, "format")
Dataset for Day 5
You’ll work with two tables:
🧾 Customer Table
-
Customer ID
-
Customer Name
-
Region
💰 Sales Table
-
Customer ID
-
Order Date
-
Sales Amount
Your task is to connect them using Customer ID.
Exercises (Hands-On)
Exercise file: Day5_Excel_Lookups_Exercise.xlsx
Exercise 1: Merge Tables
-
Use XLOOKUP to pull:
-
Customer Name
-
Region
into the Sales table using Customer ID.
-
✔ Result: One clean table with sales + customer info.
Exercise 2: Flag High-Value Customers
-
Create a new column: Customer Tier
-
Rule:
-
Sales ≥ 50,000 →
"High Value" -
Else →
"Standard"
-
✔ Use the IF function.
Exercise 2: Flag High-Value Customers
-
Create a new column: Customer Tier
-
Rule:
-
Sales ≥ 50,000 →
"High Value" -
Else →
"Standard"
-
✔ Use the IF function.
What to Expect After Completion of Day 5
After finishing Day 5, you will be able to:
✅ Merge multiple datasets like a real analyst
✅ Apply business rules directly in Excel
✅ Identify high-value customers automatically
✅ Read and understand analyst-level Excel files
✅ Feel confident handling multi-table data
👉 You’re now doing actual analyst work, not just learning functions.
Conclusion
Today you crossed from basic Excel user → analytical thinker.
Lookups and logic are the backbone of dashboards, reports, and decision-making.
From here on, Excel becomes a tool for insight, not just calculations
Related Course:
Mastering Excel Logic & Lookups: A Practical Guide to XLOOKUP, IF, and TEXT
https://www.wisemoneyai.com/2026/01/mastering-excel-logic-lookups-practical.html
https://www.wisemoneyai.com/2026/01/mastering-excel-logic-lookups-practical.html
