XLOOKUP Explained: The Formula That's Replacing VLOOKUP

If you've ever wrestled with VLOOKUP's limitations—counting columns, dealing with leftward lookups, or handling errors—XLOOKUP is about to become your new best friend. Microsoft introduced this function to solve virtually every frustration Excel users have had with lookup functions for decades.

The Problem

VLOOKUP has served us well, but it comes with serious baggage:

• Column counting nightmare: Insert a column and your formula breaks
• No leftward lookups: Your lookup value must be in the leftmost column
• Error handling: Need a separate IFERROR wrapper
• Approximate match by default: Often causes unexpected results

The Solution

XLOOKUP is a modern, flexible lookup function that addresses all these issues and more. It looks up a value in a range and returns a corresponding value from another range—in any direction.

What You'll Need

• Excel Version: Microsoft 365 or Excel 2021+
• Sample data: Any table with data you want to look up
• No add-ins required

Step 1: Understanding XLOOKUP Syntax

The basic syntax is elegantly simple:

Formula:

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

How it works:

• lookup_value - The value you're searching for
• lookup_array - The range where you're searching
• return_array - The range containing the value to return
• [if_not_found] - Optional: what to return if no match (replaces IFERROR!)
• [match_mode] - Optional: exact match, wildcard, or approximate
• [search_mode] - Optional: search direction

Step 2: Your First XLOOKUP

Let's say you have an employee table where:

• Column A (A2:A100): Employee ID
• Column B (B2:B100): Employee Name
• Column C (C2:C100): Department
• Column D (D2:D100): Salary

To find an employee's name by their ID in cell F2:

Formula:

=XLOOKUP(F2, A2:A100, B2:B100)

That's it! No column numbers, no confusing TRUE/FALSE arguments.

Step 3: Adding Error Handling (Built-In!)

One of XLOOKUP's best features is built-in error handling. Instead of wrapping your formula in IFERROR:

Formula:

=XLOOKUP(F2, A2:A100, B2:B100, "Employee Not Found")

How it works:

• If the ID isn't found, Excel displays "Employee Not Found" instead of #N/A
• Cleaner, more readable, fewer nested functions

Step 4: Lookup in Any Direction

Remember when VLOOKUP couldn't look left? XLOOKUP doesn't care about direction.

If you have Employee Name in column A and want to find their ID in column B:

Formula:

=XLOOKUP(F2, A2:A100, B2:B100)

The return array can be anywhere—left, right, even on a different sheet!

Step 5: Returning Multiple Columns

Want to return multiple pieces of data at once? XLOOKUP can do that too.

Formula:

=XLOOKUP(F2, A2:A100, B2:D100)

This returns the Employee Name, Department, AND Salary in one formula, spilling across three cells.

The Complete Solution

Here's a real-world formula that demonstrates XLOOKUP's power:

=XLOOKUP(F2, A2:A100, B2:D100, "Not Found", 0, 1)

Formula Breakdown

Common Variations

Variation 1: Case-Sensitive Lookup

=XLOOKUP(F2, A2:A100, B2:B100, "Not Found", 0)

The default is case-insensitive. For case-sensitive, you'll need a helper column with EXACT function.

Variation 2: Wildcard Search

=XLOOKUP("*"&F2&"*", A2:A100, B2:B100, "Not Found", 2)

The 2 in match_mode enables wildcard matching—perfect for partial text searches.

Variation 3: Search From Bottom (Last Match)

=XLOOKUP(F2, A2:A100, B2:B100, "Not Found", 0, -1)

The -1 searches from last to first, returning the last matching value.

Variation 4: Approximate Match (For Ranges)

=XLOOKUP(F2, A2:A100, B2:B100, "Not Found", -1)

The -1 match mode finds the next smaller value—perfect for tax brackets or grade scales.

Pro Tips

• Use table references: =XLOOKUP(F2, Table1[ID], Table1[Name]) makes formulas self-documenting
• Combine with other functions: XLOOKUP returns values that work seamlessly with SUM, IF, and others
• Two-way lookup: Nest XLOOKUP inside XLOOKUP for matrix-style lookups
• Replace INDEX-MATCH: XLOOKUP is essentially INDEX-MATCH in one function

Troubleshooting

XLOOKUP vs VLOOKUP Comparison

Conclusion

XLOOKUP isn't just an improvement over VLOOKUP—it's a complete reimagining of how lookup functions should work. The syntax is cleaner, the functionality is broader, and the error handling is built right in.

Start using XLOOKUP today, and you'll wonder how you ever tolerated VLOOKUP's limitations. Your spreadsheets will be more robust, your formulas will be easier to read, and you'll save countless hours debugging column number errors.

Welcome to the future of Excel lookups!