XLOOKUP Function in Excel: A Comprehensive Guide|2025

Discover how to use the XLOOKUP function in Excel to perform powerful and flexible lookups. Master this advanced tool for efficient data retrieval and streamlined workflows!

Microsoft Excel is one of the most widely used spreadsheet applications, owing to its powerful functionalities and formulas that allow users to organize, analyze, and manipulate data effectively. Among these formulas, XLOOKUP is one of the most powerful and versatile functions introduced in Excel 365 and Excel 2021. XLOOKUP was designed to replace older lookup functions such as VLOOKUP, HLOOKUP, and LOOKUP, providing a more efficient, flexible, and straightforward way to retrieve data from a range or table. This paper explores the XLOOKUP function in detail, comparing it with VLOOKUP, discussing common issues like XLOOKUP not working, and showcasing examples of how to use XLOOKUP with multiple criteria and across different sheets.

What is XLOOKUP in Excel?

The XLOOKUP function is a modern Excel function that replaces older lookup functions like VLOOKUP and HLOOKUP. It allows users to search for a specific value in a table or range and return a corresponding value from another column or row. XLOOKUP eliminates some of the major limitations of older lookup functions, such as the inability to perform reverse lookups and the restriction on searching only to the right of the lookup value.

Syntax of XLOOKUP Function

The syntax for the XLOOKUP function is as follows:

• lookup_value: The value you want to search for in the lookup array.
• lookup_array: The range or array that contains the value you want to look up.
• return_array: The range or array from which to return the corresponding value.
• [if_not_found] (optional): The value to return if the lookup value is not found. By default, XLOOKUP returns an error if the value is not found.
• [match_mode] (optional): The match type to use for the lookup. It can be 0 (exact match), -1 (exact match or next smaller item), or 1 (exact match or next larger item).
• [search_mode] (optional): The search direction. It can be 1 (search from first to last) or -1 (search from last to first).

XLOOKUP Formula in Excel with Example

To better understand how XLOOKUP works, let’s explore an example:

Imagine you have a list of employee names and their respective salaries, and you want to look up the salary of a specific employee.

In this case, to look up the salary of “Steve”, you would use the following formula:

Here, “Steve” is the lookup_value, A2:A5 is the lookup_array containing the employee names, and B2:B5 is the return_array containing the salaries. The result would be 55,000, the salary of Steve.

XLOOKUP vs VLOOKUP

The introduction of XLOOKUP led to the gradual obsolescence of the older lookup functions like VLOOKUP. While both functions can retrieve values from a table or range, there are several key differences between XLOOKUP and VLOOKUP.

1. Flexibility in Lookup Direction:
   • VLOOKUP can only search for the lookup value in the first column of a table and return a corresponding value from columns to the right.
   • XLOOKUP, on the other hand, can search in any column or row and return values from any other column or row, not necessarily to the right. This makes XLOOKUP much more versatile.
2. Handling Missing Values:
   • VLOOKUP returns an error (typically #N/A) if the lookup value is not found.
   • XLOOKUP allows you to define a custom value to return when the lookup value is not found, such as “Not Found” or any other text or value.
3. Performance:
   • VLOOKUP requires you to specify the column number for the result, which can be cumbersome if the table structure changes (i.e., if columns are rearranged).
   • XLOOKUP eliminates the need to reference column numbers, as it directly refers to the column from which to retrieve the result.
4. Approximate Match:
   • VLOOKUP defaults to an approximate match unless specified as an exact match using the fourth argument.
   • XLOOKUP defaults to an exact match but can be configured to perform an approximate match as well.

XLOOKUP Not Available in Excel

Despite being available in Excel 365 and Excel 2021, some older versions of Excel (Excel 2019 and earlier) do not have access to the XLOOKUP function. If you are working with a version of Excel that does not support XLOOKUP, you can use alternatives such as VLOOKUP or INDEX-MATCH, which can mimic some of the functionalities of XLOOKUP, though with more complexity.

To check whether XLOOKUP is available in your version of Excel, you can simply try typing the function in a cell. If Excel recognizes it, then you have access to it. If it does not, you will need to upgrade to a version that supports XLOOKUP.

How to Use XLOOKUP in Excel with Two Sheets

XLOOKUP is highly effective when you need to retrieve data from multiple sheets. Suppose you have two sheets: “Sheet1” and “Sheet2”. Sheet1 contains employee names, and Sheet2 contains their salaries.

Sheet1:

Sheet2:

To find the salary of “Steve” from Sheet2 while working in Sheet1, use the following formula:

In this formula:

• A2 is the lookup value from Sheet1 (the employee name).
• Sheet2!A2:A5 is the lookup range from Sheet2 (employee names).
• Sheet2!B2:B5 is the return range from Sheet2 (salaries).

XLOOKUP Exact Match

When using XLOOKUP, you can specify that you want an exact match. By default, XLOOKUP performs an exact match search, but you can explicitly indicate it by setting the match_mode argument to 0.

For example, to search for an exact match of “John” in a list of names, use the following formula:

This will search for “John” and return the corresponding value from column B (salary). If “John” is not found, the function will return “Not Found”.

XLOOKUP Return Array

One of the powerful features of XLOOKUP is the ability to return an array of values rather than a single result. This allows you to perform advanced lookups without needing additional functions.

For example, if you wanted to return a list of salaries for a range of employees, you can use the XLOOKUP function to retrieve multiple matching values. However, note that this functionality is more commonly used in the context of dynamic arrays, which is supported in Excel 365 and Excel 2021.

XLOOKUP Not Working

If XLOOKUP is not working, there could be several reasons behind the issue:

1. Version Incompatibility: As mentioned earlier, if you’re using a version of Excel older than Excel 365 or Excel 2021, XLOOKUP will not be available.
2. Incorrect Syntax: Double-check your function’s syntax and ensure all arguments are correct.
3. Array Mismatch: Ensure that the lookup_array and return_array are of the same size and shape.
4. Data Formatting: Ensure that the data you’re searching through is formatted consistently (e.g., no leading or trailing spaces, correct data types).
5. Circular References: Avoid circular references in your formulas, as these can prevent XLOOKUP from working properly.

Conclusion

The XLOOKUP function in Excel is a game-changer for those looking to simplify and streamline their data lookups. With its improved flexibility, ease of use, and ability to handle more complex lookup scenarios, XLOOKUP offers significant advantages over older functions like VLOOKUP and HLOOKUP. Whether you’re working across multiple sheets, performing exact matches, or returning entire arrays, XLOOKUP provides the tools to work more efficiently and effectively with your data.