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.