XLOOKUP FunctionThe XLOOKUP function is a lookup that will work either as a vertical lookup or a horizontal lookup. NOTE: XLOOKUP is only available in MS 365 or Excel 2021 or later. Here are the key points about the Excel XLOOKUP function:
This short video, by the Microsoft 365 team, shows a simple example of using the Excel XLOOKUP function. 
How Could You Use XLOOKUP?The XLOOKUP function is in the Lookup Function category of Excel functions. You can use the XLOOKUP function to return values from a lookup table. For example:
These four examples are shown in the sections below XLOOKUP SyntaxThe XLOOKUP function has the following 5 arguments in its syntax:
The first three arguments are required:
These three arguments are listed in bold font, in the Function Arguments dialog box shown below. The remaining three arguments are optional:
These three arguments are listed in normal font, in the Function Arguments dialog box shown above. 
XLOOKUP: Pros
XLOOKUP: Cons

Try XLOOKUP  Interactive ExamplesIn the embedded Excel workbook shown below, you can see XLOOKUP function examples, and try tthis function for yourself  even if XLOOKUP is not available in your version of Excel.
This workbook uses Excel for the Web, where XLOOKUP is available, along with more of the newest Excel functions, Tip: At the bottom right of the embedded workbook, click the icon to open the workbook online, full sized.

Ex 1: Item Name for Product CodeIn this example, we'll use XLOOKUP to get the item name from a lookup table, for a specific product code. Here's where all of the information is located on the worksheet:
XLOOKUP FormulaHere is the formula in cell G2, with references to cell F2, and the table columns named Code and Item:
In cell G2, the formula result is the product name, "Sweater". In the lookup table shown below, you can see that Sweater is the correct result:
The next section has details on how this formula works. 
How XLOOKUP Formula WorksHere is the formula in cell G2, with the function's four arguments colour coded:
In that formula, the XLOOKUP function uses these 4 arguments:
To get the result, the XLOOKUP function:

Ex 2: Latest Product PriceIn this example, we'll use XLOOKUP to get the latest price from a lookup table, for a specific product code. Here's where all of the information is located on the worksheet:
IMPORTANT: For this example, the lookup table is sorted by the Date column, from earliest date to latest date. 
XLOOKUP Formula  Latest PriceHere is the formula in cell B3, with references to cell A3, and the table columns named Code and Price:
In cell B3, the formula result is the price of 32. In the lookup table shown below, you can see that Sweater is the correct result:
The next section has details on how this formula works. 
How XLOOKUP Formula WorksHere is the formula in cell G2, with the function's first 3 arguments colour coded:
In that formula, the XLOOKUP function uses these 6 arguments:
To get the result, the XLOOKUP function:

Ex 3: Rate  Approx MatchIn this example, we'll use XLOOKUP to get the commission rate from a lookup table, using an approximate match. There are two lookup tables, to show the two ways an approximate match can be found:
In the following tables, the XLOOKUP function does these steps::
XLOOKUP Formula  Approximate MatchNext SmallerHere is the formula in cell B12, where formula should look for an exact match for the amount in cell A2, or the next smaller amount:
Next LargerHere is the formula in cell E12, where formula should look for an exact match for the amount in cell A2, or the next larger amount:

Ex 4: Get Multiple ColumnsIn this example, we'll use XLOOKUP to return the values from 3 columns in a lookup table, for a specific product code. In the following table, the XLOOKUP function does these steps::
XLOOKUP Formula  Multiple ColumnsHere is the formula in cell G2, using table references.
The table name is tblProductsXLK2, the lookup column is named Code, and the return column is named Item. 
XLOOKUP Reference StylesWhen you're building a formula in Excel, you can click on a worksheet cell, to refer to that cell in your formula. Depending on where that cell is located, Excel can create a:
Examples on This PageThe examples on this page use the following reference types:
For example, this formula has a worksheet reference to cell F2, and structured table references to the Code and Item columns in the table named tblProductsXLK2
Worksheet Cell ReferencesEven though Excel automatically creates structured table references for table cells, you can use worksheet cell references, if you prefer. For example, here is the same formula (shown above), with worksheet addresses, instead of table references.
You might find that reference style easier to read, if you've been using Excel for a long time, like I have! Also, using this cell reference style makes it easier to copy formulas across a row, when you need an absolute reference to one or more of the table columns. The video below shows that problem. Video: Copy Formulas with Table ReferencesThis video shows the problem when copying formulas with table references, and two ways to prevent it. There are written steps on the Excel Named Tables page, and a video timeline below the video. Video Timeline

More XLOOKUP ResourcesThese additional resources are on the Microsoft website. Examples: Find more XLOOKUP examples on the Microsoft site. Questions: For help with the XLOOKP function, you can ask questions in Microsoft's Excel Tech Community or get support in Microsoft's Answers community. 
Get the Practice File

More Lookup Functions 
Last updated: August 20, 2023 10:09 AM