The Complete Guide to Excel XLOOKUP Functions

XLOOKUP is one of the most anticipated and useful updates to Excel in years. It fixes many of the challenges that Excel professionals have found when using the more traditional VLOOKUP formula. We’ll explain what these changes are, how to use the formula, and why people are so excited.

We explain how to use XLOOKUP in Excel, why it's better than VLOOKUP and how to avoid and troubleshoot issues that arise

The Excel XLOOKUP formula is utilized to bring in values from one column based on the values in a cell. It’s often used to consolidate data into a single table or to combine data from multiple spreadsheets. Historically, people would use a VLOOKUP or HLOOKUP formula that had many limitations, such as only being able to look in one direction or it would return the wrong value when multiple values existed in a return column. XLOOKUP fixes many of these issues.

Let’s dive in!

The History of XLOOKUP and Why it Was Introduced

In August 2019, Microsoft announced the release of XLOOKUP for Excel. The function was designed to succeed and improve upon the classic VLOOKUP by addressing its well-known limitations. XLOOKUP simplifies the lookup process, requiring fewer arguments for common lookups, less limitations and offers advanced options for more complex searches.

Benefits compared to VLOOKUP include:

  • Flexibility in Search Direction – It can search both vertically and horizontally.
  • Simpler Syntax – It requires fewer arguments, making it easier to use and understand.
  • Return Any Column – It can return values from columns to the left of the search column.
  • Handles Missing Values – It allows for a default return value if the search key is not found.
  • Improved Performance – It’s optimized for performance, especially on large datasets.
  • Eliminates Column Sorting – It can find the next highest or lowest value as defined in the function.

Even with the advancements included with XLOOKUP, Excel will continue to support VLOOKUP and HLOOKUP will continue to be supported ensuring that all existing workbooks still calculate okay meaning that the use of XLOOKUP is completely optional for long time Excel users.

If you’re new to Excel, it can be beneficial to familiarize yourself with the nuances of VLOOKUP as it will help you appreciate the ease of use of XLOOKUP in comparison.

Is XLOOKUP Better Than VLOOKUP?

XLOOKUP is considered an improvement over VLOOKUP due to its versatility and simplicity. Unlike VLOOKUP, which only searches for data in the first column of a table and returns a value from a column to the right, XLOOKUP can search in any direction.

This flexibility eliminates the need to rearrange your data before performing a lookup, making XLOOKUP a more efficient and user-friendly option. In our option, XLOOKUP is better than VLOOKUP in many ways, but we still use VLOOKUP at times because of how well understood the formula syntax is

When to Use XLOOKUP

XLOOKUP is designed to find data within a table or range and return a corresponding value from any other column in your Excel Workbook. It’s used as a way to combine datasets based on the common value of a row and apply it to each cell individually.

The following example shows an Excel spreadsheet with two datasets. There is a Customer Number in both tables, and you want to combine the Customer Name into a single table.

Example of a lookup value needed to explain when to use an XLOOKUP formula

The XLOOKUP function can be used to lookup the value in column G, Customer Number and return any of the the other columns on the sheet, such as Customer Name, Customer Address, etc.

How to Use the Excel XLOOKUP Function

The XLOOKUP function in Excel allows you to search a range for a certain value and return the corresponding value from range. The simplest syntax for XLOOKUP is: XLOOKUP(lookup_value, lookup_array, return_array) with additional parameters optional.

The screenshot below shows an XLOOKUP formula being used to look at the value in D3 and return the corresponding value in column H.

Example of using an XLOOKUP column with a simple syntax to re-create the effect of a VLOOKUP by using a lookup value, a lookup column and a return column

You’ll notice that the lookup_array and return_array in this case are next to eachother and the formula returns results similar to a VLOOKUP without having to define the number of columns to the right that the target is from the formula.

Using Excel XLOOKUP with Different Arrays (Columns)

When setting up a basic XLOOKUP formula, you define the value to lookup, the column to look it up in, and the column to return. A big difference between XLOOKUP and VLOOKUP is that the data doesn’t have to be continuous. Meaning that instead of defining the lookup_array as a table, you can dfine a specific column and then define the column of values you want to return.

Here’s an example of an XLOOKUP being used to return values based on the column letter of both the lookup and return columns.

Example of using an XLOOKUP when the return column is not next to the lookup column

This is one of the most common scenarios when using XLOOKUP in Excel, and extremely common within the accounting and finance world. The added benefit is that you won’t have to adjust the number count of columns if the data on the right changes like you would in a VLOOKUP.

Note: An XLOOKUP can be deployed to lookup columns, and it can also be deployed to lookup rows. This means that it can replace both a VLOOKUP and an HLOOKUP.

The Full Syntax of XLOOKUP Explained

In addition to the ability to return columns or rows based on a common value, XLOOKUP provides users with several advanced options that solve common Excel problems that occurred when using VLOOKUP. Options include the ability to define a value if nothing is found, more granular match modes and search directions.

Going beyond our basic example from above, the full syntax of XLOOKUP is as follows:

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

The sections of each part of the formula are as follows,. The parts of the formula above in [ brackets ] are optional parameters that are helpful but not specifically required.

  • lookup_value: The value you are searching for.
  • lookup_array: The array or range where you want to find the lookup_value.
  • return_array: The array or range from which to return a value.
  • if_not_found (optional): What to return if the lookup_value is not found. This is optional.
  • match_mode (optional): Specifies the match type: exact match (0), exact match or next smaller item (-1), exact match or next larger item (1), wildcard match (2). The default is 0 for an exact match.
  • search_mode (optional): Specifies the search mode: search first-to-last (1), search last-to-first (-1), binary search on sorted data ascending (2), or binary search on sorted data descending (-2).

Defining XLOOKUP Results If No Value is Found

XLOOKUP gives users the option of defining the return value when no match is found. Similar to wrapping a VLOOKUP with the IFNA function, but it’s included as part of the syntax. Place the text you want to return in quotes after defining the return column.

Here’s an example of an XLOOKUP returning the value “No Cookie!” if no Customer is found.

An example of an XLOOKUP column that returns a value when the value does not exist in the return column as an alternative to VLOOKUP IFNA

For reference, a VLOOKUP would look like this: IFNA(VLOOKUP(D10,G:H,2,0),"No Cookie!")

The XLOOKUP version is much cleaner and only requires knowledge of a single formula rather than knowing about and how to use an IFNA formula.

XLOOKUP Match Modes

The match mode in XLOOKUP allows users to specify how closely the values in the lookup array should match the lookup value. This feature is essential when the exact match is not required or available.

The different match modes available in Excel XLOOKUP are:

  • Exact Match (0) – Use this mode when you need to find an exact match for your lookup value. It’s the most common scenario, ensuring that only a perfect match returns a result.
  • Exact Match or Next Smaller Item (-1) – This is useful in financial analyses or when dealing with ranges or thresholds. For instance, finding the appropriate tax rate for an income level that does not have an exact match in the table.
  • Exact Match or Next Larger Item (1) – Ideal for predictive or forward-looking analyses, where you might need to approximate to the next higher value if an exact match isn’t found, such as estimating future sales targets.
  • Wildcard Match (2) – Utilize this mode for searches that need to accommodate partial matches, using ? to replace a single character or * for multiple characters. It’s particularly useful in filtering tasks or when dealing with data that includes variations in spelling or abbreviations.

Exact match is the default when no optional parameters are defined for match mode.

XLOOKUP Search Modes

The search mode in XLOOKUP defines the direction and method of the search. Historically you would have to sort a list of return values for VLOOKUP to appropriately find the first value in the list when multiple matches were found.

  • First-to-Last (1) – The default mode, it searches from the beginning of the array, suitable for most lookup tasks where the data is not sorted or the first occurrence is required.
  • Last-to-First (-1) – Use this when you need the last occurrence of the lookup value, especially useful in time-series data or when the most recent entry is needed.
  • Binary Search on Sorted Data (2 or -2) – Employ binary search for significantly faster lookups in large, sorted datasets. Use 2 for ascending order and -2 for descending. This mode is ideal for performance-critical tasks where data is pre-sorted and consistency in data sorting can be maintained.

The Disadvantage of XLOOKUP

The biggest disadvantages of XLOOKUP are the lack of availability in Excel versions older than Excel 2019 and the limited understanding of the formula by many Excel that have relied on VLOOKUP, HLOOKUP, and Index Match for years or decades.

With the added functionalities and simplicity of performing many tasks that would take multiple steps or multiple formulas to a achieve into one single well documented function we expect that XLOOKUP will slowly replace many of the other lookup functions that have been around for years.

However, we would also caution people new to Microsoft Excel to learn the basics of VLOOKUP and Index Match formulas as they will likely need to understand them in pre-existing workbooks.

Why Does XLOOKUP Never Work?

If you encounter issues with XLOOKUP, it might be due to compatibility issues, incorrect syntax, or misunderstanding of its arguments. Ensuring you’re using a supported version of Excel and double-checking your formula syntax can help mitigate these problems.

An XLOOKUP that fails to find any values resulting in many #VALUE! errors that need troubleshooting

When you encounter issues with XLOOKUP, here are some troubleshooting steps to walk through:

  • Compatibility Issues – XLOOKUP won’t work in Excel versions older than 2019.
  • Incorrect Syntax – Errors in formula syntax, such as incorrect argument order or missing arguments, can lead to errors.
  • Incorrect Range Definition – Ensure that ranges are set separately to show lookup column and target column.
  • Data Type Mismatch – Ensure the data types in the lookup_value and lookup_array match. Text and numbers are treated differently in Excel.
  • Forget What You Know About VLOOKUP – Even though they are related functions, the syntax is different enough that you can’t rely on prior VLOOKUP knowledge to deploy XLOOKUP.

Is VLOOKUP Outdated?

XLOOKUP’s introduction addresses many of VLOOKUP’s limitations. However, VLOOKUP is so universally used and understood by Excel users that it still can be found in hundreds of millions of Excel workbooks. It may even be faster to deploy for seasoned Excel professionals compared to the newer XLOOKUP formula.

We recommend people invest time in understanding both formulas. Likely the future will be more and more use of XLOOKUP but there will still be many times where VLOOKUP is utilized instead.

XLOOKUP is Better than VLOOKUP

XLOOKUP is considered a better formula than VLOOKUP due to its flexibility, ease of use, and ability to overcome many of VLOOKUP’s limitations. It represents a significant step forward in Excel’s function capabilities. For people new to Excel, we would recommend making XLOOKUP your go-to formula for ease of use and to avoid many of the pitfalls that exist with a more traditional VLOOKUP or HLOOKUP formula.

Conclusion

XLOOKUP is a testament to Excel’s ongoing evolution. It offers users a more powerful and flexible formula for various lookup tasks. While VLOOKUP has served many well over the years, XLOOKUP’s introduction is a game-changer, and arguably one of the biggest updates to Excel in years which speaks to the ubiquity of the VLOOKUP formula and the various workarounds that data professionals have had to come up with to get around its limitations.

Whether you’re a seasoned Excel professional or a casual user, understanding and utilizing XLOOKUP can significantly improve your spreadsheet efficiency. However, we will caution people not to completely forget about VLOOKUP, HLOOKUP, and Index Match as they’re still very widely used and it could take decades for the popularity of XLOOKUP to match that of VLOOKUP.

Scroll to Top