What Is the XLOOKUP Function in Excel? How to Use It?

The XLOOKUP function is useful when you need to find something in a table or range. Learn how to use the XLOOKUP function in Excel.

If you use Excel a lot, you have probably used LOOKUP functions quite often. If you’re not familiar, the LOOKUP function is used to search through a single row or column to find a specific value in another row or column.

This function comes in handy when working with a lot of data in an Excel sheet. In Excel, there are several types of LOOKUP functions.

In this article, we will tell you everything you need to know about the XLOOKUP function in Microsoft Excel, along with some helpful examples.

What Is XLOOKUP Function in Excel?

XLOOKUP replaces the old functions like HLOOKUP, VLOOKUP, and LOOKUP. It supports vertical and horizontal lookups.

In other words, the XLOOKUP function allows you to quickly find a value in a given dataset, both horizontally and vertically, and returns its corresponding value in a different row or column.

There are many ways to use the XLOOKUP function to get the most out of it. We’ll take a look at some of the XLOOKUP examples that would give you a better understanding of it.

How to Access XLOOKUP Function

Unfortunately, XLOOKUP is not available for users running Office 2010, 2013, 2016, and 2019 versions. It is only available on the Microsoft Office 365 suite.

If you’re not using Microsoft 365 suite on your computer, you might need to upgrade to Microsoft 365 to get access to the XLOOKUP function. And if you are already using Microsoft 365, then you will find the option already enabled in Excel. XLOOKUP is also available on Office 365 Online.

Syntax for XLOOKUP Function

XLOOKUP function’s syntax is similar to that of VLOOKUP and HLOOKUP. If you have ever used them, you’ll find using XLOOKUP more convenient. Here’s the syntax for the XLOOKUP function in Excel:

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

XLOOKUP function supports up to six arguments, and here are their values.

1. lookup_value (required): the value that you want to find.
2. lookup_array (required): the array where you want to look for the lookup value.
3. return_array (required): the array from where you want to fetch and return the values when the lookup value is found.
4. [if_not_found] (optional): When the match isn’t found, this is the value to be returned.
5. [match_mode] (optional): This argument lets you specify the type of match you’re looking after. There are different values to specify it:
   • 0 – It looks for an exact match, and the value should exactly match the value in the lookup_array. It is also set as default when not mentioned.
   • -1 – It looks for the exact match and, once found, returns to the next smaller value.
   • 1 – It looks for the exact match and once found returns to the next larger value.
   • 2 – It does partial matching using wildcards where *, ?, and ~ have special meaning.
6. [search_mode] (optional): Used to specify the XLOOKUP’s search mode in lookup_array. There are different values to specify the same:
   • 1 – Performs search starting from the first item. It’s set as default when nothing is specified.
   • -1 – Performs reverse search starting at the last item.
   • 2 – Performs a binary search in the lookup_array where the data needs sorting in ascending order. If the data isn’t sorted, it may produce errors or wrong results.
   • -2 – Performs a binary search in the lookup_array where the data needs sorting in descending order. If the data isn’t sorted, it may produce errors or wrong results.

Advantages and Disadvantages of XLOOKUP Function in Excel

XLOOKUP is still a function that has its advantages over VLOOKUP and INDEX/MATCH. But it has its disadvantages too.

Advantages of XLOOKUP Function

• It works both vertically and horizontally.
• Needs three arguments, instead of four arguments in the VLOOKUP and INDEX MATCH functions.
• Always defaults to an exact match.
• Can perform partial match lookups using wildcards.
• Can perform lookups in descending order.
• Uses one function instead of two in INDEX MATCH.

Disadvantages of XLOOKUP Function

• Optional arguments may look complex to beginners.
• Can consume more time when selecting two ranges, and there are too many cells in the spreadsheet.
• Returns an error when lookup and array returns are not of the same length.
• Need to remember both lookup and return ranges.

How to Use the XLOOKUP Function in Excel

The XLOOKUP function works similarly to the LOOKUP function in Excel. You can use XLOOKUP by simply selecting cell references where the function should operate.

Alternatively, you can also use the “Formula bar” box located at the top; and insert the XLOOKUP function syntax there.

Example 1    uses XLOOKUP to look up a country name in a range, and then return its telephone country code. It includes the lookup_value (cell F2), lookup_array (range B2:B11), and return_array (range D2:D11) arguments. It doesn’t include the match_mode argument, as XLOOKUP produces an exact match by default.

Note: XLOOKUP uses a lookup array and a return array, whereas VLOOKUP uses a single table array followed by a column index number. The equivalent VLOOKUP formula, in this case, would be: =VLOOKUP(F2,B2:D11,3,FALSE)

———————————————————————————

Example 2    looks up employee information based on an employee ID number. Unlike VLOOKUP, XLOOKUP can return an array with multiple items, so a single formula can return both employee name and department from cells C5:D14.

———————————————————————————

Example 3    adds an if_not_found argument to the preceding example.

———————————————————————————

Example 4    looks in column C for the personal income entered in cell E2, and finds a matching tax rate in column B. It sets the if_not_found argument to return 0 (zero) if nothing is found. The match_mode argument is set to 1, which means the function will look for an exact match, and if it can’t find one, it returns the next larger item. Finally, the search_mode argument is set to 1, which means the function will search from the first item to the last.

Note: XARRAY’s lookup_array column is to the right of the return_array column, whereas VLOOKUP can only look from left-to-right.

Ref: https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929