Master XLOOKUP in Excel: Replace VLOOKUP Forever

VLOOKUP has been Excel's lookup workhorse for decades. But it's also frustrating: it only looks right, breaks when you insert columns, and returns cryptic errors. You've memorized the syntax, learned the workarounds, and accepted its limitations.

XLOOKUP changes everything. It searches in any direction, handles errors gracefully, and does in one formula what used to take nested functions. Once you learn XLOOKUP, you'll never go back to VLOOKUP.

What You'll Learn

• XLOOKUP syntax and basic usage
• Advantages over VLOOKUP and INDEX-MATCH
• Handling errors with custom messages
• Finding last match, approximate match, and wildcards
• Returning entire rows or multiple columns
• Real-world examples and best practices

Why VLOOKUP is Broken

VLOOKUP forces you to work around its limitations:

• Only searches right: Lookup column must be leftmost
• Column numbers break: Insert a column, update every formula
• No error handling: Returns #N/A instead of useful messages
• Can't find last match: Only finds the first occurrence
• Approximate match confusion: Fourth argument defaults to TRUE (rarely what you want)

XLOOKUP: The Modern Solution

XLOOKUP launched in Excel 365 and fixes every VLOOKUP pain point:

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

What makes it better:

• Searches in any direction (left, right, up, down)
• Returns entire rows or columns
• Built-in error handling
• Can find last match or approximate match
• Column insertions don't break formulas

Basic XLOOKUP Syntax

Simple Example

Find an employee's department:

1=XLOOKUP(E2, A2:A100, C2:C100)

Breaking it down:

• E2: What you're looking for (employee ID)
• A2:A100: Where to search (employee ID column)
• C2:C100: What to return (department column)

That's it. Three arguments for the most common use case.

VLOOKUP vs XLOOKUP

Old way (VLOOKUP):

1=VLOOKUP(E2, A2:D100, 3, FALSE)

Problems:

• Must count columns (error-prone)
• Department must be right of lookup column
• No built-in error handling

New way (XLOOKUP):

1=XLOOKUP(E2, A2:A100, C2:C100)

Benefits:

• Reference columns directly (no counting)
• Works regardless of column order
• Cleaner, more readable

Handling Errors Gracefully

Custom Error Messages

Instead of #N/A when nothing is found:

1=XLOOKUP(E2, A2:A100, C2:C100, "Employee not found")

The fourth argument (if_not_found) displays when lookup fails. Much better than cryptic errors.

Practical examples:

1// Product lookup with helpful error
2=XLOOKUP(A2, Products!A:A, Products!B:B, "Product ID not in catalog")
3
4// Customer lookup
5=XLOOKUP(B2, Customers!A:A, Customers!C:C, "New customer - add to database")
6
7// Budget code lookup
8=XLOOKUP(C2, Codes!A:A, Codes!D:D, "Invalid code - check with finance")

Advanced Search Modes

Exact, Approximate, and Wildcard Matches

The match_mode parameter controls how XLOOKUP finds results:

1=XLOOKUP(lookup_value, lookup_array, return_array, if_not_found, match_mode)

Match modes:

• 0 (default): Exact match
• -1: Exact match or next smaller
• 1: Exact match or next larger
• 2: Wildcard match (* ? ~)

Example: Tax Bracket Lookup

Find tax rate based on income using approximate match:

1=XLOOKUP(D2, $A$2:$A$8, $B$2:$B$8, "Error", 1)

Tax bracket table:

Income of $95,000 returns 24% (next larger threshold).

Example: Wildcard Matching

Find products containing specific text:

1=XLOOKUP("*laptop*", A2:A100, B2:B100, "No match", 2)

Finds first product with "laptop" anywhere in the name.

Wildcard characters:

• *: Any number of characters
• ?: Single character
• ~: Escape character (find literal * or ?)

Finding Last Match

VLOOKUP always finds the first match. XLOOKUP can find the last with search_mode:

1=XLOOKUP(lookup_value, lookup_array, return_array, if_not_found, match_mode, search_mode)

Search modes:

• 1 (default): Search first to last
• -1: Search last to first
• 2: Binary search ascending (fastest, requires sorted data)
• -2: Binary search descending

Example: Most Recent Transaction

Find customer's latest purchase date:

1=XLOOKUP(E2, A2:A1000, D2:D1000, "No purchases", 0, -1)

Searches from bottom up, returning the last matching date.

Returning Multiple Columns

XLOOKUP can return entire rows or multiple columns at once.

Return Entire Row

Get all employee information:

1=XLOOKUP(E2, A2:A100, B2:F100)

Returns name, department, email, phone, and location as a row of values.

Return Multiple Specific Columns

Combine with other functions for selective columns:

1// Return name and email only (columns B and D)
2=XLOOKUP(E2, A2:A100, B2:B100)&" - "&XLOOKUP(E2, A2:A100, D2:D100)

Or use multiple XLOOKUPs in adjacent cells to build a custom result row.

Real-World Examples

Example 1: Order Lookup System

Build a order details retriever:

Data structure:

• Column A: Order ID
• Column B: Customer Name
• Column C: Order Date
• Column D: Total
• Column E: Status

Formulas:

1// Cell G2: Enter Order ID to lookup
2// Cell G3: Customer Name
3=XLOOKUP(G2, A2:A1000, B2:B1000, "Order not found")
4
5// Cell G4: Order Date
6=XLOOKUP(G2, A2:A1000, C2:C1000, "")
7
8// Cell G5: Order Total
9=XLOOKUP(G2, A2:A1000, D2:D1000, "")
10
11// Cell G6: Status with color coding
12=XLOOKUP(G2, A2:A1000, E2:E1000, "")

Add conditional formatting to G6 based on status values.

Example 2: Price List with Tiered Pricing

Volume-based pricing lookup:

Pricing tiers:

Formula (exact or next smaller):

1=XLOOKUP(B2, $A$2:$A$6, $B$2:$B$6, "Invalid quantity", 1)

Order of 250 units returns $9.00 (tier for 100-499 units).

Example 3: Multi-Criteria Lookup

Combine criteria using concatenation:

Find price based on product AND region:

1// Helper column (Column D): Product&Region
2=A2&B2
3
4// Lookup formula
5=XLOOKUP(F2&G2, D2:D100, C2:C100, "Not available in this region")

Or use array formulas (advanced):

1=XLOOKUP(1, (A2:A100=F2)*(B2:B100=G2), C2:C100, "Not found")

Example 4: Dynamic Drop-Down Lists

Create cascading dependent dropdowns:

Setup:

1. Category selected in B2
2. Show only products for that category in B3

Named ranges:

• All_Products: A2:A100
• All_Categories: B2:B100

Data validation for B3:

1// Custom list formula
2=FILTER(All_Products, All_Categories=B2, "No products")

Then use XLOOKUP to retrieve product details when selected.

XLOOKUP vs INDEX-MATCH

INDEX-MATCH has been the power user's alternative to VLOOKUP. How does XLOOKUP compare?

INDEX-MATCH:

1=INDEX(C2:C100, MATCH(E2, A2:A100, 0))

XLOOKUP:

1=XLOOKUP(E2, A2:A100, C2:C100)

Winner: XLOOKUP for simplicity, but INDEX-MATCH still useful for:

• Complex 2D lookups
• Offsetting from match position
• When you don't have Excel 365

Common Errors and Solutions

Performance Considerations

When XLOOKUP is Fast

✅ Good performance:

• Small to medium datasets (under 50,000 rows)
• Exact match lookups
• Binary search mode with sorted data

When to Optimize

⚠️ May need optimization:

• Hundreds of XLOOKUP formulas on one sheet
• Very large datasets (100,000+ rows)
• Complex workbooks with circular references

Optimization techniques:

1. Use binary search when data is sorted:

   excelCopy

   1=XLOOKUP(E2, A2:A100000, C2:C100000, "", 0, 2)

2. Convert to values after calculating:

   • Copy formulas → Paste Values
   • Removes calculation overhead

3. Consider Power Query for large datasets:

   • Better performance for big data
   • Refresh manually vs. real-time calculation

Converting VLOOKUP to XLOOKUP

Conversion Formula

VLOOKUP:

1=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Equivalent XLOOKUP:

1=XLOOKUP(lookup_value, first_column_of_table, column_to_return, "Not found", 0)

Practical Conversion Examples

Before (VLOOKUP):

1=VLOOKUP(A2, Products!$A$2:$F$500, 4, FALSE)

After (XLOOKUP):

1=XLOOKUP(A2, Products!$A$2:$A$500, Products!$D$2:$D$500, "Not found")

Advantages gained:

• Can insert columns in Products table without breaking formula
• Custom error message instead of #N/A
• More readable (references column D directly)

Copy-Paste Formulas

Basic Lookup Template

1=XLOOKUP(lookup_cell, data_range_column, return_range_column, "Not found")

Lookup with Approximate Match

1=XLOOKUP(lookup_cell, data_range_column, return_range_column, "Not found", 1)

Last Match Lookup

1=XLOOKUP(lookup_cell, data_range_column, return_range_column, "Not found", 0, -1)

Wildcard Lookup

1=XLOOKUP("*"&search_text&"*", data_range_column, return_range_column, "Not found", 2)

Multi-Column Return

1=XLOOKUP(lookup_cell, data_range_column, multi_column_range, "Not found")

Key Takeaways

• XLOOKUP is the modern replacement for VLOOKUP and often INDEX-MATCH
• Simpler syntax: Three arguments for basic lookups
• Built-in error handling: Custom messages instead of #N/A
• Flexible direction: Search and return from any columns
• Advanced features: Last match, approximate match, wildcards
• Excel 365 only: Older versions must use VLOOKUP or INDEX-MATCH

Conclusion

XLOOKUP represents Excel's evolution toward simpler, more powerful formulas. It takes the most common spreadsheet task—looking up values—and makes it intuitive and robust.

If you're still using VLOOKUP out of habit, spend 15 minutes converting a few formulas to XLOOKUP. You'll immediately appreciate the cleaner syntax, better error messages, and flexibility. Within a week, XLOOKUP will become your default lookup function.

Your VLOOKUP days are over. Welcome to the future of Excel lookups.

Related articles: XLOOKUP Explained: Replacing VLOOKUP, INDEX MATCH: Dynamic Duo in Excel