The XLOOKUP Function in Microsoft Excel

The XLOOKUP Function in Microsoft Excel makes it easier to find and retrieve data from a worksheet. It is designed as a modern alternative to older lookup functions such as VLOOKUP and HLOOKUP, while offering more flexibility for common data-searching tasks.

Whether you are working with customer records, product lists, employee information, sales reports, or financial data, XLOOKUP can help you quickly return the information you need.

What Is The XLOOKUP Function in Microsoft Excel?

XLOOKUP is an Excel function used to search for a value in one range and return a corresponding value from another range.

The basic syntax is:

=XLOOKUP(lookup_value, lookup_array, return_array)

For example, suppose you have product IDs in column A and product prices in column B. You can use:

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

Excel searches for the value in cell E2 within A2:A100 and returns the matching price from B2:B100.

Why Use XLOOKUP?

XLOOKUP provides several advantages over traditional lookup formulas.

It can:

• Search vertically or horizontally
• Return data from columns on either side of the lookup column
• Perform exact matches by default
• Display a custom message when no match is found
• Search from first to last or last to first
• Return multiple values when appropriate
• Work with separate lookup and return ranges

These features make XLOOKUP particularly useful for modern Excel worksheets.

XLOOKUP Formula Example

Imagine a worksheet containing this information:

If cell E2 contains P1003, you can retrieve the product name with:

=XLOOKUP(E2,A2:A5,B2:B5)

The result is:

Monitor

To retrieve the price instead, use:

=XLOOKUP(E2,A2:A5,C2:C5)

The result is:

$180

How to Use XLOOKUP Step by Step

Follow these steps to create a basic XLOOKUP formula.

1. Identify the Lookup Value

Determine what you want Excel to search for. This could be a product ID, employee number, customer name, or invoice number.

For example:

E2

2. Select the Lookup Array

The lookup array contains the values Excel should search.

A2:A100

3. Select the Return Array

The return array contains the information you want Excel to retrieve.

C2:C100

4. Create the Formula

Combine the three elements:

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

Press Enter, and Excel returns the corresponding result.

How to Handle Missing Results

One useful feature of XLOOKUP is the ability to specify what should appear when Excel cannot find a match.

Use:

=XLOOKUP(E2,A2:A100,C2:C100,"Not Found")

Instead of displaying an error, Excel returns:

Not Found

You can also use a more descriptive message:

=XLOOKUP(E2,A2:A100,C2:C100,"Product ID not found")

This is especially helpful when creating reports that will be viewed by other users.

XLOOKUP vs. VLOOKUP

XLOOKUP can simplify many tasks that traditionally required VLOOKUP.

With VLOOKUP, the lookup column generally needs to be the first column of the selected table. XLOOKUP separates the lookup and return ranges, so the return data does not need to be positioned to the right of the lookup data.

For example:

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

This directly tells Excel which range to search and which range to return.

By comparison, a VLOOKUP formula uses a table range and column number:

=VLOOKUP(E2,A2:C100,2,FALSE)

XLOOKUP can therefore make formulas easier to understand and maintain.

XLOOKUP for Horizontal Searches

XLOOKUP is not limited to vertical data.

You can also search across a row.

For example:

=XLOOKUP(B1,B1:G1,B2:G2)

This searches across the first row and returns the corresponding value from the second row.

This can be useful for monthly reports, financial tables, and other horizontally organized data.

Using XLOOKUP With an Exact Match

XLOOKUP uses exact matching by default, which makes it convenient for searching IDs and other unique values.

For example:

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

You do not need to add a separate FALSE argument for a basic exact lookup.

This can reduce formula complexity and help prevent accidental approximate matches.

Searching From the Last Result

XLOOKUP can also search from the bottom of a range.

The formula uses the search_mode argument:

=XLOOKUP(E2,A2:A100,C2:C100,"Not Found",0,-1)

The -1 tells Excel to search from the last item toward the first.

This can be useful when a dataset contains multiple records for the same customer, product, or transaction and you want the most recent matching entry based on the order of the data.

Using XLOOKUP With Wildcards

XLOOKUP can perform wildcard searches when configured for wildcard matching.

For example:

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

The * wildcard represents any number of characters.

This can help when you need to find text that contains a particular word or phrase rather than an exact value.

Common XLOOKUP Errors

Although XLOOKUP is powerful, incorrect ranges or data can still cause problems.

#N/A

This usually means Excel could not find the requested value.

You can provide a custom message:

=XLOOKUP(E2,A2:A100,B2:B100,"No match")

Incorrect Range Sizes

The lookup and return arrays should normally contain corresponding numbers of rows or columns.

For example, avoid mismatched ranges such as:

=XLOOKUP(E2,A2:A100,C2:C50)

Use matching ranges instead:

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

Extra Spaces

Text values containing unwanted spaces can prevent a match.

For example, Product A and Product A may not behave as expected in a lookup.

Cleaning your source data can help avoid these problems.

Tips for Using XLOOKUP Efficiently

Keep your lookup formulas simple and consistent. When working with large worksheets, consider converting your data into an Excel Table so that formulas can automatically adjust as new records are added.

It is also useful to:

• Use meaningful cell references
• Keep lookup and return ranges aligned
• Add a custom not-found message
• Check for duplicate values
• Clean unnecessary spaces from text
• Avoid unnecessarily large ranges
• Test formulas with several sample records

Frequently Asked Questions

What is XLOOKUP used for in Excel?

XLOOKUP searches for a value in one range and returns a related value from another range.

Is XLOOKUP better than VLOOKUP?

XLOOKUP offers more flexible lookup and return ranges and supports additional search options. It is designed to handle many common lookup tasks more conveniently than VLOOKUP.

What is the basic XLOOKUP formula?

The basic syntax is:

=XLOOKUP(lookup_value, lookup_array, return_array)

How do I show text instead of #N/A?

Add a custom if_not_found value:

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

Can XLOOKUP search horizontally?

Yes. XLOOKUP can search both vertically and horizontally, making it suitable for different worksheet layouts.

Conclusion

The XLOOKUP Function in Microsoft Excel is a flexible way to search worksheets and retrieve related information. Its separate lookup and return ranges, exact-match behavior, custom error messages, and additional search options make it useful for everything from simple lists to larger business reports.

Once you understand the basic formula, you can use XLOOKUP to replace many traditional lookup formulas and create cleaner, more flexible Excel worksheets.