Mastering Excel Logic & Lookups: A Practical Guide to XLOOKUP, IF, and TEXT

 

Introduction

Excel is more than just a spreadsheet tool—it’s a decision-making engine. At the heart of that engine are three essential functions: XLOOKUP, IF, and TEXT.

Whether you’re analyzing sales data, validating records, building dashboards, or preparing reports for stakeholders, these functions help you:

• Retrieve the right data

• Apply business logic

• Present information in a clear and professional format

This lesson provides an in-depth, practical explanation of XLOOKUP, IF, and TEXT—covering how each function works, when to use them, their advantages and limitations, and how they are commonly combined in real-world analyst tasks. By the end, you’ll not only understand the syntax, but also think like an analyst when choosing the right function.

1️⃣ XLOOKUP — Modern Lookup Powerhouse

What XLOOKUP Does

XLOOKUP searches for a value in one range and returns a corresponding value from another range.

It replaces:

• VLOOKUP

• HLOOKUP

• INDEX + MATCH

Basic Syntax

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Arguments Explained (ALL OPTIONS)

1. lookup_value (required)

The value you want to find

➡ Example: Customer ID, Product Code, Date

2. lookup_array (required)

The range where Excel looks for the value

➡ Must be one-dimensional (single row or column)

3. return_array (required)

The range containing the value to return

➡ Can be left, right, above, or below the lookup array

4. if_not_found (optional)

Value returned if lookup fails

=XLOOKUP(A2, A:A, B:B, "Not Found")

✅ Advantage over VLOOKUP: no need for IFERROR

5. match_mode (optional)

Mode	Meaning
0	Exact match (default)
-1	Exact or next smaller
1	Exact or next larger
2	Wildcards (* ? ~)

Example (wildcard):

=XLOOKUP("*apple*", A:A, B:B, ,2)

6. search_mode (optional)

Mode	Meaning
1	First to last (default)
-1	Last to first
2	Binary search (ascending)
-2	Binary search (descending)

 

Example (latest record):

=XLOOKUP(A2, A:A, B:B, ,0,-1)

Advanced XLOOKUP Use Cases

✔ Multiple Criteria Lookup

=XLOOKUP(1, (A:A=E1)*(B:B=E2), C:C)

✔ Return Entire Row or Column

=XLOOKUP(A2, A:A, B:D)

✔ Approximate Ranges (Salary Bands, Grades)

=XLOOKUP(score, min_scores, grades, , -1)

Advantages of XLOOKUP

✅ Searches left or right

✅ No column number dependency

✅ Built-in error handling

✅ Supports arrays

✅ Cleaner formulas

✅ Faster than INDEX+MATCH in many cases 

 

Limitations of XLOOKUP

❌ Not available in very old Excel versions

❌ Binary search requires sorted data

❌ Slightly slower than VLOOKUP for extremely large legacy files

❌ Can return #SPILL! if space is blocked

2️⃣ IF — Logical Decision Engine

What IF Does

Tests a condition and returns different results based on TRUE or FALSE.

Basic Syntax

=IF(logical_test, value_if_true, value_if_false)

Logical Tests You Can Use

• =

• <>

• >

• <

• >=

• <=

Example:

=IF(B2>=50000,"High Value","Regular")

Variations & Extensions

✔ Nested IF

=IF(A2>=90,"A",IF(A2>=80,"B","C"))

⚠ Hard to maintain beyond 3–4 levels

✔ IF with AND / OR

=IF(AND(A2>=80,B2="Yes"),"Pass","Fail")

=IF(OR(A2="Admin",A2="Manager"),"Full Access","Limited")

 

✔ IF with Text, Numbers, Dates

=IF(A2="", "Missing", A2)

  =IF(A2<TODAY(),"Overdue","On Track")

 

✔ IF + Lookup (Very Common)

 =IFERROR(XLOOKUP(A2,A:A,B:B),"Not Found")

Advantages of IF

✅ Simple and intuitive

✅ Foundation of Excel logic

✅ Combines with almost any function

✅ Essential for data validation and flags

Limitations of IF

❌ Nested IF becomes unreadable

❌ Complex logic is hard to debug

❌ Performance drops with many nested conditions

💡 Better alternatives for complex logic

• IFS

• SWITCH

• Lookup tables

• Power Query rules

3️⃣ TEXT — Formatting Without Changing Values

What TEXT Does

Formats numbers, dates, or times as text strings using custom formats.

Basic Syntax

=TEXT(value, format_text)

Common TEXT Formats

Numbers

=TEXT(A2,"#,##0")

=TEXT(A2,"#,##0.00")

=TEXT(A2,"0%")

Currency

=TEXT(A2,"$#,##0.00")

Dates

=TEXT(A2,"yyyy-mm-dd")

=TEXT(A2,"dd-mmm-yyyy")

=TEXT(A2,"mmmm")

Time

 =TEXT(A2,"hh:mm AM/PM")

Custom Examples

="INV-" & TEXT(A2,"00000")

=TEXT(TODAY(),"dddd")

TEXT with Other Functions

✔ TEXT + IF

=IF(A2>0, TEXT(A2,"#,##0"), "N/A")

✔ TEXT + CONCAT

="Sales: " & TEXT(B2,"$#,##0")

Advantages of TEXT

✅ Clean reporting output

✅ Preserves numeric formatting

✅ Ideal for dashboards and labels

✅ Makes IDs and codes readable

Limitations of TEXT

❌ Converts numbers to text

❌ Cannot be used directly in math afterward

❌ Sorting behaves differently

❌ Overuse can break calculations

💡 Best practice: Use TEXT only for presentation, not calculations

When to Use Each (Quick Decision Guide) 

Goal	Best Function
Find matching data	XLOOKUP
Apply business rules	IF
Display formatted values	TEXT
Replace VLOOKUP	XLOOKUP
Create flags	IF
Labels for dashboards	TEXT

Analyst Tip

Strong Excel analysts don’t just know formulas — they know when not to use them.

• Use XLOOKUP for relationships

• Use IF for decisions

• Use TEXT only for final presentation

• For heavy logic → consider Power Query or helper tables

 

Conclusion

XLOOKUP, IF, and TEXT form the core logic layer of Excel analysis. When used correctly, they allow you to connect datasets, enforce business rules, and transform raw numbers into insights that decision-makers can understand.

• XLOOKUP simplifies data retrieval and replaces older, error-prone lookup methods.

• IF enables conditional logic that reflects real-world business decisions.

• TEXT enhances presentation and reporting without altering underlying data.

Mastering these functions is not about memorizing formulas—it’s about knowing when to apply each one and how to combine them effectively. These skills are foundational for data analysts, finance professionals, operations teams, and anyone preparing for Excel-heavy roles or technical interviews.

Once you’re comfortable with these functions, you’ll be well-prepared to move into more advanced topics such as dashboards, automation, Power Query, and business intelligence tools.

 

 

 

Data Analyst for Beginners in 30 Days: Day 4: Excel Fundamentals for Analysts

 

Introduction

Welcome to Day 4! Today is where data starts to feel powerful. You’ll learn how analysts use Excel to turn raw rows into clear, meaningful numbers that support business decisions. Don’t worry—this is beginner-friendly and hands-on.

By the end of today, you’ll be comfortable opening a dataset, applying core formulas, and building a simple metrics table like a real analyst

What You’ll Learn Today

1️⃣ Core Excel Formulas (The Analyst Essentials)

We’ll focus on three formulas you’ll use every single day as an analyst:

🔢 SUM

• What it does: Adds numbers together

• Why analysts use it: Total sales, total revenue, total units sold

Example:

=SUM(B2:B101) → Adds all sales values

AVERAGE

• What it does: Finds the mean value

• Why analysts use it: Average order value, average daily sales

Example:

=AVERAGE(B2:B101)




COUNT

• What it does: Counts cells with numbers

• Why analysts use it: Number of transactions, number of records

Example:

=COUNT(A2:A101)

💡 Analyst tip: COUNT only counts numbers, not text. 




Dataset: Sales Transactions (CSV)

You’ll work with a simple Sales Transactions CSV, typically containing:

• Date

• Order ID

• Product

• Quantity

• Sales Amount

This mirrors real business data you’d get from finance, e-commerce, or operations teams.

Hands-On Exercise: Create a Basic Metrics Table

🧪 Exercise Instructions

1. Open the Sales Transactions CSV in Excel

2. Create a new sheet called Metrics

3. Build this table:

✅ Expected Output

A clean metrics table that answers:

• How much did we sell?

• What’s the average sale?

• How many transactions happened?

This is exactly how analysts start reports.

What to Expect After Completion of Day 4

After finishing Day 4, you will be able to:

• Confidently navigate Excel like an analyst

• Apply core formulas to real datasets

• Create simple KPI and metrics tables

• Understand how raw data becomes business insight

• Be ready for Day 5: Data Cleaning & Data Quality

This is a huge milestone—Excel is a non-negotiable skill for analysts

Common Beginner Mistakes (Avoid These!)

• Using text cells in SUM or AVERAGE

• Forgetting to lock ranges when copying formulas

• Mixing raw data and metrics in the same sheet

You’re already ahead by learning this early

Conclusion

Excel is the foundation tool of data analysis, and today you’ve taken your first real analyst step. These formulas may look simple—but they power dashboards, reports, and executive decisions every day.

Mastering this means you’re officially thinking like an analyst


Next Lesson:

Data Analyst for Beginners in 30 Days: Day 5: Excel Lookups and Logic
https://www.wisemoneyai.com/2026/01/data-analyst-for-beginners-in-30-days_24.html