Formulas > Lookup > XLOOKUP

Excel XLOOKUP Function Examples

Use Microsoft Excel XLOOKUP function for vertical search in a column, or horizontal search in a row. Like VLOOKUP and HLOOKUP combined, but better! Return related values from same row or column. Available in MS 365 or Excel 2021 or later

XLOOKUP Function

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

• It looks down a specified column (vertical) OR across a specified row (horizontal), to find a value.
• XLOOKUP will return the following results::
• Vertical lookup:
• If the return array is a single column, it will return a single cell.
• If the return array is multiple columns, the row
• Horizontal lookup:
• If the return array is a single row, it will return a single cell.
• If the return array is multiple rows, the column

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:

1. Get item name for product code
2. Return latest product price
3. Find commission rate for price range
4. Return multiple columns from lookup table

These four examples are shown in the sections below

XLOOKUP Syntax

The XLOOKUP function has the following 5 arguments in its syntax:

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

The first three arguments are required:

• lookup_value - Value to search for
• lookup_array- Where to look for the lookup value - range (row or column), or array
• return_array- Where get return value - range (row or column), or array

These three arguments are listed in bold font, in the Function Arguments dialog box shown below.

The remaining three arguments are optional:

• [if_not_found]- Value to return if a valid match is not found
  • If not used, and valid match not found, #N/A error is returned
• [match_mode]- Type of match to return - exact or wildcard
  • If not used, the default of match type 0 (exact) is used
• [search_mode]- Where to start search - from first item or last item
  • If not used, default search mode (0 - from first item) is used

These three arguments are listed in normal font, in the Function Arguments dialog box shown above.

XLOOKUP: Pros

• Very flexible -- look for value in any row or column.
• Can find an exact or approximate match
• Can find first or last matching item
• By using 2 XLOOKUP functions in one formula, can find both the row and the column
• Can return multiple cells using SPILL
• Can use multiple criteria
• Can find "*" and "?" as a character, as well as the option to use them as a wildcard.
• Result is a cell or range, so there is the option to use the address in another formula such as SUM, instead of simply returning the value from the cell(s).
• These are not volatile functions, so they have less impact on calculations in a large workbook

XLOOKUP: Cons

• Only available in MS 365 or Excel 2021 or later
• Using 2 XLOOKUPs to find row and column is less intuitive than the similar INDEX / MATCH -MATCH function combination

Try XLOOKUP - Interactive Examples

In 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.

• NOTE: The interactive file might not be viewable on all devices or browsers.

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 Code

In 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 formula will be entered in cell G2
• Product code has been entered in cell F2
• Lookup table is named tblProductsXLK2
• Product code lookup column is named Code
• Item names to return are in column named Item

XLOOKUP Formula

Here is the formula in cell G2, with references to cell F2, and the table columns named Code and Item:

• =XLOOKUP($F$2,  tblProductsXLK2[Code],  tblProductsXLK2[Item],  "Not Found")

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:

• product code SW001 is in cell C5
• product name from that row, in cell A5, is Sweater

The next section has details on how this formula works.

How XLOOKUP Formula Works

Here is the formula in cell G2, with the function's four arguments colour coded:

• =XLOOKUP(
  1. $F$2,
  2. tblProductsXLK2[Code],
  3. tblProductsXLK2[Item],
  4. "Not Found")

In that formula, the XLOOKUP function uses these 4 arguments:

1. lookup_value - look for product code entered in cell F2
2. lookup_array- look in the Code column
3. return_array- return related value the Item column
4. [if_not_found]- if product code isn't found, show result, "Not Found"

To get the result, the XLOOKUP function:

• finds the code, SWoo1, in the Code column, in cell C5
• returns product name from the same row, in Item column, cell A5

Ex 2: Latest Product Price

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

• XLOOKUP formula will be entered in cell B3
• Product code has been entered in cell A3
• Lookup table is namedtblProd_b
• Product code lookup column is named Code
• Item prices to return are in column named Price

IMPORTANT: For this example, the lookup table is sorted by the Date column, from earliest date to latest date.

XLOOKUP Formula - Latest Price

Here is the formula in cell B3, with references to cell A3, and the table columns named Code and Price:

• =XLOOKUP($A3, tblProd_b[Code], tblProd_b[Price], "Not Found", 0, -1)

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:

• searching from the bottom up, JK001 is in cell A12
• in that row, the price is 32

The next section has details on how this formula works.

How XLOOKUP Formula Works

Here is the formula in cell G2, with the function's first 3 arguments colour coded:

• =XLOOKUP(
  1. $F$2,
  2. tblProductsXLK2[Code],
  3. tblProductsXLK2[Item],
  4. "Not Found"
  5. 0
  6. -1)

In that formula, the XLOOKUP function uses these 6 arguments:

1. lookup_value - look for product code entered in cell F2
2. lookup_array- look in the Code column
3. return_array- return related value the Item column
4. [if_not_found] - if product code isn't found, show result, "Not Found"
5. [match_mode]- 0 - find an exact match for the product code
6. [search_mode]- -1 - start search from last item in lookup column

To get the result, the XLOOKUP function:

• starting from last row, moving up, finds JKoo1, in Code column, in cell A12
• returns product price from the same row, in Price column, cell E12

XLOOKUP Reference Styles

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

1. worksheet cell reference, such as A2
2. structured table reference, such as tblSales[Region]
3. named range reference, such as SelCode

Examples on This Page

The examples on this page use the following reference types:

1. worksheet cell references for cells that are not in a named Excel table
2. structured table references for lookup columns in named Excel tables
3. there are no named range references in these examples

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

• =XLOOKUP($F$2,  tblProductsXLK2[Code],  tblProductsXLK2[Item],  "Not Found")

Worksheet Cell References

Even 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.

• =XLOOKUP($F$2, $C$4:$C$12,  $A$4:$A$12,  "Not Found")

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 References

This 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

• 00:00 Introduction
• 00:46 SUBTOTAL Formula to Check Totals
• 01:19 SUMIFS Formula with Table References
• 01:42 Copy the Formula Down One Row
• 02:13 Copy Across
• 03:09 Copy With No Problems
• 03:45 Get More Information

More XLOOKUP Resources

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

To 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

INDEX / MATCH Functions

Compare Lookup Functions

VLOOKUP

CHOOSE

LOOKUP

Lookup - 2 Criteria