How to Use XLOOKUP in Excel

Microsoft Excel is widely used for organizing, analyzing, and retrieving data. One of the most common tasks in Excel is looking up values from a table. For many years, functions like VLOOKUP and HLOOKUP were the primary tools for this job. However, they came with several limitations that often confused users or required complex workarounds.

To solve these problems, Microsoft introduced XLOOKUP, a modern and flexible lookup function designed to replace VLOOKUP, HLOOKUP, and even some uses of INDEX and MATCH. XLOOKUP is more powerful, easier to read, and significantly more versatile.

In this article, you will learn what XLOOKUP is, how it works, its syntax, practical examples, advanced use cases, common errors, and best practices. By the end, you will have a solid understanding of how to confidently use XLOOKUP in real-world Excel scenarios.

What Is XLOOKUP?

XLOOKUP is a lookup and reference function in Excel that searches for a value in one range and returns a corresponding value from another range. Unlike older lookup functions, XLOOKUP works both vertically and horizontally, supports exact matches by default, and allows searching in any direction.

XLOOKUP is available in:

• Microsoft Excel for Microsoft 365
• Excel 2021 and later versions

It is not available in Excel 2019 or earlier.

Why Use XLOOKUP Instead of VLOOKUP or HLOOKUP?

Before learning how to use XLOOKUP, it helps to understand why it is considered superior.

Limitations of Older Lookup Functions

• VLOOKUP can only search from left to right.
• HLOOKUP can only search from top to bottom.
• Both require you to count column or row numbers manually.
• They break if columns or rows are inserted.
• Approximate match behavior can be risky if not configured correctly.

Advantages of XLOOKUP

• Searches in any direction
• No need to count columns or rows
• Exact match by default
• Handles missing values gracefully
• Can return multiple values
• Supports advanced match and search modes

These improvements make XLOOKUP easier to learn and safer to use.

XLOOKUP Syntax Explained

The basic syntax of XLOOKUP is:

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

Let’s break down each argument in detail.

1. lookup_value

The value you want to find. This could be:

• A number
• Text
• A cell reference
• A formula result

Example:

A2

2. lookup_array

The range where Excel searches for the lookup value. This is usually a single row or column.

Example:

A2:A10

3. return_array

The range containing the value you want returned. This must be the same size as the lookup_array.

Example:

B2:B10

4. if_not_found (optional)

The value returned if the lookup value is not found. This avoids errors like #N/A.

Example:

"Not Found"

5. match_mode (optional)

Controls how Excel matches the lookup value.

Options include:

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

6. search_mode (optional)

Controls the direction of the search.

Options include:

• 1 – Search from first to last (default)
• -1 – Search from last to first
• 2 – Binary search (ascending)
• -2 – Binary search (descending)

Basic XLOOKUP Example

Imagine you have the following data:

If you want to find the price of the product with ID 102, you can use:

=XLOOKUP(102, A2:A4, C2:C4)

Excel searches column A for 102 and returns the corresponding value from column C.

Using XLOOKUP with Text Values

XLOOKUP works just as well with text.

Example:

=XLOOKUP("Monitor", B2:B4, C2:C4)

This formula looks for “Monitor” in the Product Name column and returns its price.

Handling Missing Values with IF_NOT_FOUND

One of the most helpful features of XLOOKUP is the ability to handle missing values gracefully.

Without IF_NOT_FOUND:

=XLOOKUP(105, A2:A4, B2:B4)

Result:

#N/A

With IF_NOT_FOUND:

=XLOOKUP(105, A2:A4, B2:B4, "Product not available")

Result:

Product not available

This improves readability and user experience.

Using XLOOKUP Instead of VLOOKUP

Here is a direct comparison.

VLOOKUP Example

=VLOOKUP(A2, A2:C10, 3, FALSE)

XLOOKUP Equivalent

=XLOOKUP(A2, A2:A10, C2:C10)

The XLOOKUP version:

• Is easier to read
• Does not rely on column numbers
• Does not break if columns change

Horizontal Lookups with XLOOKUP

Unlike VLOOKUP, XLOOKUP works horizontally without switching functions.

Example data:

Formula:

=XLOOKUP("Feb", B1:D1, B2:D2)

This replaces HLOOKUP entirely.

Using Wildcards in XLOOKUP

XLOOKUP supports wildcards when using match_mode 2.

Wildcards:

• * matches any number of characters
• ? matches one character

Example:

=XLOOKUP("Mon*", B2:B10, C2:C10, "Not Found", 2)

This matches any text starting with “Mon”.

Searching from Last to First

XLOOKUP can search from the bottom up, which is useful for finding the most recent entry.

Example:

=XLOOKUP(A2, A2:A20, B2:B20, , 0, -1)

This returns the last matching value instead of the first.

Returning Multiple Values

XLOOKUP can return multiple columns at once if the return_array includes more than one column.

Example:

=XLOOKUP(A2, A2:A10, B2:D10)

Excel spills the result into adjacent cells automatically.

XLOOKUP with Dynamic Arrays

XLOOKUP integrates seamlessly with Excel’s dynamic array features. This allows you to build powerful dashboards and models without helper columns.

Example:

=XLOOKUP(E2:E5, A2:A10, B2:B10)

This performs multiple lookups at once.

Common XLOOKUP Errors and How to Fix Them

#N/A

Cause: Lookup value not found Solution: Use the if_not_found argument

#VALUE!

Cause: lookup_array and return_array are different sizes Solution: Ensure both ranges match exactly

#NAME?

Cause: XLOOKUP not available in your Excel version Solution: Upgrade to Excel 2021 or Microsoft 365

Best Practices for Using XLOOKUP

• Use exact match (default) whenever possible
• Always define if_not_found for cleaner results
• Use structured references with tables for clarity
• Avoid binary search unless data is sorted
• Replace old VLOOKUP formulas when updating workbooks

When Not to Use XLOOKUP

While XLOOKUP is powerful, there are cases where alternatives may be better:

• Very old Excel versions (use INDEX + MATCH)
• Extremely large datasets where performance is critical
• Compatibility with users on older Excel releases

Conclusion

XLOOKUP is one of the most significant improvements to Excel’s lookup functionality in years. It simplifies formulas, reduces errors, and provides flexibility that older functions simply cannot match. Whether you are a beginner learning Excel basics or an advanced user managing complex datasets, mastering XLOOKUP will greatly improve your efficiency and accuracy.

By understanding its syntax, options, and best practices, you can confidently replace older lookup methods and build cleaner, more reliable spreadsheets. As Excel continues to evolve, XLOOKUP stands out as the modern standard for data lookup and retrieval.