Excel XLOOKUP: Complete Guide with 10 Real-World Examples

VLOOKUP has been Excel's go-to lookup function for decades. But it's deeply flawed: can only look right, breaks when columns move, and returns errors on partial matches. INDEX-MATCH is powerful but complex—most users find it intimidating.

Enter XLOOKUP: Excel's modern lookup function that fixes everything wrong with VLOOKUP while remaining simpler than INDEX-MATCH. If you're still using VLOOKUP in 2026, you're working harder than necessary.

What is XLOOKUP?

XLOOKUP searches a range for a value and returns a corresponding result from another range. It's like asking, "Find this customer ID and tell me their email address."

Basic syntax:

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

Why XLOOKUP beats VLOOKUP:

• ✓ Looks left AND right (VLOOKUP only looks right)
• ✓ Doesn't break when you insert/delete columns
• ✓ Returns multiple columns at once
• ✓ Handles errors gracefully with custom messages
• ✓ Searches from bottom-up or exact/approximate matches
• ✓ Faster performance on large datasets

Availability: Excel 365, Excel 2021, Excel for Web. Not available in Excel 2019 or earlier.

Example 1: Basic Lookup (Replace VLOOKUP)

Scenario: Find employee email from employee ID.

Data:

Task: Look up employee E002's email.

XLOOKUP formula:

=XLOOKUP("E002", A2:A4, C2:C4)

Result: sarah@company.com

Why this is better than VLOOKUP:

• No column counting (VLOOKUP needs "3" to specify 3rd column)
• Email column can move anywhere—formula still works
• Clearer to read what you're looking up and what you're returning

Example 2: Lookup to the Left

Scenario: You have product codes in column D, but want to return product names from column B.

VLOOKUP problem: Can't look left. You'd have to rearrange columns or use INDEX-MATCH.

XLOOKUP solution:

=XLOOKUP("P-1234", D2:D100, B2:B100)

Finds P-1234 in column D, returns corresponding value from column B. Direction doesn't matter.

Example 3: Handle Errors Gracefully

Scenario: Lookup customer info, but some IDs don't exist. Instead of #N/A error, show "Not Found."

XLOOKUP formula:

=XLOOKUP(A2, CustomerIDs, CustomerNames, "Not Found")

Result:

• If ID exists → returns customer name
• If ID missing → displays "Not Found"

Why this matters: #N/A errors break downstream calculations and look unprofessional in reports.

Example 4: Return Multiple Columns

Scenario: Look up employee ID and return name, email, AND department in one formula.

XLOOKUP formula:

=XLOOKUP("E002", A2:A4, B2:D4)

Result: Returns array with all three values: Sarah Lee | sarah@company.com | Marketing

Note: In modern Excel, this spills across three columns automatically. One formula, three outputs.

Example 5: Approximate Match for Pricing Tiers

Scenario: Find pricing tier based on order quantity. Different prices for 1-10, 11-50, 51-100, 101+ units.

Data:

Task: Customer orders 75 units. What's the price?

XLOOKUP formula:

=XLOOKUP(75, A2:A5, B2:B5, , -1)

Result: $7.00 (matches 51-100 tier)

The "-1" parameter: Finds exact match or next smallest value. Perfect for tax brackets, commission tiers, shipping rates.

Example 6: Last Occurrence (Bottom-Up Search)

Scenario: Transaction log with duplicate customer IDs. Find the MOST RECENT transaction for each customer.

XLOOKUP formula:

=XLOOKUP(CustomerID, TransactionLog[Customer], TransactionLog[Date], , 0, -1)

The "-1" search mode: Searches from bottom to top, returning last occurrence instead of first.

Use cases:

• Most recent order date
• Latest status update
• Current price (from price history)
• Last login time

Example 7: Wildcard Search

Scenario: Look up product info when you only know part of the product code.

Task: Find product starting with "PRO-"

XLOOKUP formula:

=XLOOKUP("PRO-*", ProductCodes, ProductNames, , 2)

The "2" match mode: Enables wildcard matching (* = any characters, ? = single character)

Practical examples:

• Find customers with @gmail.com addresses
• Locate products in specific category prefix
• Search for files by partial name

Example 8: Two-Way Lookup (Row and Column)

Scenario: Sales table with months as columns, products as rows. Find specific product-month combination.

XLOOKUP formula (nested):

=XLOOKUP(Month, MonthHeaders, XLOOKUP(Product, ProductNames, SalesData))

What this does:

1. Inner XLOOKUP finds product row
2. Outer XLOOKUP finds month column within that row
3. Returns intersection value

Alternative: Use XLOOKUP with INDEX for cleaner syntax, or wait for XMATCH.

Example 9: Dynamic Dependent Dropdowns

Scenario: Two dropdowns—first selects category, second shows only items in that category.

Setup:

1. Name your data ranges (Ctrl+F3)
2. First dropdown: Data Validation → List of categories
3. Second dropdown formula:

=XLOOKUP(CategoryCell, Categories, ItemRanges)

Returns dynamic list based on category selection.

Example 10: Combine with IF for Conditional Logic

Scenario: Look up discount rate, but only apply if order total exceeds $500.

Formula:

=IF(OrderTotal>500, XLOOKUP(CustomerTier, TierList, DiscountList), 0)

Result:

• If order > $500 → looks up and applies discount
• If order ≤ $500 → no discount (returns 0)

Extend this: Combine XLOOKUP with SUMIFS, COUNTIFS, or other functions for powerful business logic.

XLOOKUP vs VLOOKUP vs INDEX-MATCH Comparison

Bottom line: XLOOKUP is easier than VLOOKUP and more powerful than INDEX-MATCH.

Common XLOOKUP Mistakes

Mistake 1: Wrong array sizes Lookup array and return array must have same number of rows.

❌ =XLOOKUP(A2, B2:B10, C2:C15) (10 vs 15 rows) ✅ =XLOOKUP(A2, B2:B10, C2:C10)

Mistake 2: Forgetting absolute references When copying formulas down, lookup/return arrays should be anchored.

✅ =XLOOKUP(A2, $B$2:$B$100, $C$2:$C$100)

Mistake 3: Not leveraging if_not_found Don't wrap XLOOKUP in IFERROR. Use the built-in parameter.

❌ =IFERROR(XLOOKUP(...), "Not Found") ✅ =XLOOKUP(..., "Not Found")

Mistake 4: Using approximate match unintentionally Match_mode defaults to exact match (0). But specifying -1 or 1 without sorted data causes errors.

Performance Tips

For large datasets (10,000+ rows):

1. Use sorted data with binary search:

=XLOOKUP(value, sorted_range, return_range, , , 2)

The "2" search mode enables binary search (must be sorted ascending).

1. Limit lookup array size: Use dynamic ranges or Table references instead of entire columns: ❌ =XLOOKUP(A2, B:B, C:C) (checks entire column) ✅ =XLOOKUP(A2, B2:B5000, C2:C5000) (checks only used range)

2. Consider data model for very large datasets: Import data to Power Query/Power Pivot for faster lookups on millions of rows.

When to Use XLOOKUP vs Other Functions

Use XLOOKUP when:

• Looking up values from one table to another
• Need flexibility (look left, handle errors, return multiple columns)
• Working with Excel 365 or 2021

Use VLOOKUP when:

• Sharing workbooks with users on Excel 2019 or earlier
• Simple right-side lookups (VLOOKUP still works fine)

Use INDEX-MATCH when:

• Need maximum compatibility (works in all Excel versions)
• Building complex formulas where XLOOKUP isn't available

Use Power Query when:

• Merging large datasets (100,000+ rows)
• Need repeatable data transformation
• XLOOKUP is too slow

Frequently Asked Questions

Does XLOOKUP work on older Excel versions? No. Only Excel 365, Excel 2021, and Excel for Web. Use INDEX-MATCH for older versions.

Is XLOOKUP faster than VLOOKUP? Yes, especially on large datasets. XLOOKUP is optimized for modern Excel's calculation engine.

Can XLOOKUP lookup multiple criteria? Not directly. Combine criteria into a helper column, or use FILTER + XLOOKUP.

What if my data isn't sorted for approximate match? XLOOKUPs approximate match (-1 or 1) requires sorted data. If unsorted, sort first or use exact match only.

Should I replace all my VLOOKUPs with XLOOKUP? Only if you're on Excel 365/2021. If sharing with others on older Excel, keep VLOOKUP for compatibility.

The Bottom Line

XLOOKUP is the lookup function Excel users have wanted for 30 years. It's simpler than INDEX-MATCH, more powerful than VLOOKUP, and handles edge cases elegantly.

If you're still using VLOOKUP, switch to XLOOKUP for:

• More flexible lookups (left/right doesn't matter)
• Column-insert safety (no more broken formulas)
• Cleaner error handling (custom messages built-in)
• Multiple-column returns (one formula, multiple outputs)
• Better performance (faster on large datasets)

Start with Example 1 today. Replace one VLOOKUP with XLOOKUP. See how much cleaner it is. Then gradually convert your other lookups.

Within a week, you'll wonder how you ever tolerated VLOOKUP's limitations.

---

Related articles: Master XLOOKUP: New Excel Lookup Formula, XLOOKUP Explained: Replacing VLOOKUP, INDEX MATCH: Excel's Dynamic Duo