Power Spreadsheets

Excel and VBA tutorials and training. Learn how to use Microsoft Excel and Visual Basic for Applications now.

Excel XLOOKUP in Table in 4 Easy Steps (+ Free Easy-To-Adjust Excel Workbook Example)

In this Excel XLOOKUP in Table Tutorial, you learn how to create an Excel XLOOKUP in Table formula to carry out an XLOOKUP inside an Excel Table.

The XLOOKUP function is available in Excel 2021 and later (including Excel 365).

This Excel XLOOKUP in Table Tutorial is accompanied by an Excel workbook with the data and formulas I use. Get this example workbook (for free) by clicking the button below.


Get immediate free access to the Excel XLOOKUP in Table example workbook

Related Excel Training Materials and Resources

The following Excel VLookup Tutorials may help you better understand the differences between the XLOOKUP and VLOOKUP functions (XLOOKUP vs. VLOOKUP).

This Excel XLOOKUP in Table Tutorial is part of a more comprehensive series of Excel XLOOKUP Tutorials.

You can find more Excel and VBA Tutorials in the organized Tutorials Archive: Click here to visit the Archives. The following are some of my most popular Excel Tutorials and Training Resources:

If you want to learn more about Excel essentials, Excel formulas, and similar Excel topics, you may be interested in taking one (or more) Excel Courses: Click here to learn more about these Excel Courses (affiliate link).

If you want to learn how to automate Excel (and save time) by working with macros and VBA, you may be interested in the following Premium Excel Macro and VBA Training Materials:

If you need help with Excel tasks/projects, you may be interested in working with me: Click here to learn more about working with me.


Get immediate free access to the Excel XLOOKUP in Table example workbook

Excel XLOOKUP in Table Formula Template/Structure

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup-table/
=XLOOKUP(LookupValue,TableName[HeaderForColumnWhereYouSearch],TableName[HeaderForColumnWithValueToReturn])


Get immediate free access to the Excel XLOOKUP in Table example workbook

Step-by-Step Process to Create an Excel XLOOKUP in Table Formula

(1) Call the XLOOKUP function.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup-table/
=XLOOKUP(...)

(2) Use the first argument of the XLOOKUP function (lookup_value) to specify the lookup value. This is the value you search for in the column you search in (and specify in step #3).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup-table/
=XLOOKUP(LookupValue,...)

(3) Use the second argument of the XLOOKUP function (lookup_array) to specify the lookup array. This is the column where you search for the lookup value (you specified in step #2).

To create an Excel XLOOKUP in Table formula, use a structured reference to specify the lookup array inside the Table you work with. As a general rule: Specify the lookup array as a single-column (1 column, several rows) cell range by using the following structured reference components:

  • The Table's name (TableName); followed by
  • The column's header, wrapped in square brackets ([HeaderForColumnWhereYouSearch]).
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup-table/
=XLOOKUP(LookupValue,TableName[HeaderForColumnWhereYouSearch],...)

(4) Use the third argument of the XLOOKUP function (return_array) to specify the return array. This is the column with the value to return.

To create an Excel XLOOKUP in Table formula, use a structured reference to specify the return array inside the Table you work with. As a general rule: Specify the return array as a single-column (1 column, several rows) cell range by using the following structured reference components:

  • The Table's name (TableName); followed by
  • The column's header, wrapped in square brackets ([HeaderForColumnWithValueToReturn]).
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup-table/
=XLOOKUP(LookupValue,TableName[HeaderForColumnWhereYouSearch],TableName[HeaderForColumnWithValueToReturn])


Get immediate free access to the Excel XLOOKUP in Table example workbook

How (and Why) the XLOOKUP in Table Formula Works

When you create an Excel Table, Excel assigns names to:

  • The Table; and
  • The Table's column headers.

You can use these names to create structured references (instead of explicit cell references, such as “A1”).

The XLOOKUP function:

  1. Searches a range or array for a match; and
  2. Returns the corresponding item(s) from a second range or array.

You can use structured references to create an Excel XLOOKUP in Table formula. In other words: You can:

  • Create an Excel XLOOKUP in Table formula that works with:
    • An Excel Table; and
    • Structured references; to
  • Carry out an XLOOKUP inside the Excel Table.


Get immediate free access to the Excel XLOOKUP in Table example workbook

Excel XLOOKUP in Table Example Formula

This Excel XLOOKUP in Table Tutorial is accompanied by an Excel workbook with the data and formulas I use. Get this example workbook (for free) by clicking the button below.


Get immediate free access to the Excel XLOOKUP in Table example workbook

Excel XLOOKUP in Table Example Worksheet

The example worksheet has 3 tables/sections with the following characteristics:

(1) Excel Table (cells A6 to H26).

An Excel Table (named MyTable):

  • With the data.
  • Where I search with the XLOOKUP in Table example formula.

(2) Lookup value (cells J6 and K6).

The lookup value (Salesperson 8) is stored in cell K6.

(3) XLOOKUP in Table example formula (cells J8 to K9).

  • Cell J9:
    • Stores the XLOOKUP in Table example formula; and
    • Displays the results.
  • Cell K9 displays the XLOOKUP in Table example formula I enter in cell J9.
Example worksheet for Excel XLOOKUP in Table

Excel XLOOKUP in Table Example Formula

The XLOOKUP in Table example formula stored in cell J9 is as follows:

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup-table/
=XLOOKUP(K6,MyTable[Salesperson],MyTable[Total Sales])

The XLOOKUP function:

  • Searches for the lookup value stored in cell K6 (Salesperson 8) in MyTable's Salesperson column (column A); and
  • Returns the corresponding item from MyTable's Total Sales column (column H).

Therefore, the XLOOKUP in Table example formula:

  • Finds the lookup value (Salesperson 8) in row 17 of the Salesperson column (cell A17); and
  • Returns the corresponding value (6,999.3) stored in the Total Sales column (cell H17).
Excel XLOOKUP in Table example formula


Get immediate free access to the Excel XLOOKUP in Table example workbook

Download the Excel XLOOKUP in Table Example Workbook

This Excel XLOOKUP in Table Tutorial is accompanied by an Excel workbook with the data and formulas I use. Get this example workbook (for free) by clicking the button below.


Get immediate free access to the Excel XLOOKUP in Table example workbook

Related Excel Training Materials and Resources

The following Excel VLookup Tutorials may help you better understand the differences between the XLOOKUP and VLOOKUP functions (XLOOKUP vs. VLOOKUP).

This Excel XLOOKUP in Table Tutorial is part of a more comprehensive series of Excel XLOOKUP Tutorials.

You can find more Excel and VBA Tutorials in the organized Tutorials Archive: Click here to visit the Archives. The following are some of my most popular Excel Tutorials and Training Resources:

If you want to learn more about Excel essentials, Excel formulas, and similar Excel topics, you may be interested in taking one (or more) Excel Courses: Click here to learn more about these Excel Courses (affiliate link).

If you want to learn how to automate Excel (and save time) by working with macros and VBA, you may be interested in the following Premium Excel Macro and VBA Training Materials:

If you need help with Excel tasks/projects, you may be interested in working with me: Click here to learn more about working with me.


Get immediate free access to the Excel XLOOKUP in Table example workbook

Subscribe
Notify of
guest
1 Comment
Most Voted
Newest Oldest
Inline Feedbacks
View all comments

I publish a lot of Tutorials and Training Resources about Microsoft Excel and VBA. Here are some of my most popular Excel Training Resources:

  1. Free Excel VBA Email Course
  2. Excel Macro Tutorial for Beginners
  3. Excel Power Query (Get and Transform) Tutorial for Beginners
  4. Excel Keyboard Shortcut Cheat Sheet
  5. Excel Resources
Terms and Conditions

Privacy Policy

Limit of Liability and Disclaimer of Warranty

Affiliate Disclosure