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 usingthe 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:

XLOOKUP Reference StylesWhen you're building a formula in Excel, you an 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 ResourcesExamples: 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 FileTo see the XLOOKUP function examples from this page, download the XLOOKUP function sample workbook. The zipped file is in xlsx format, and does not contain any macros. 
More Lookup Functions 
Last updated: January 7, 2023 11:57 AM