Data Analyst for Beginners in 30 Days: Day 5: Excel Lookups and Logic

 

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