Power Spreadsheets

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

Excel Nested XLOOKUP (Dynamic Lookup Value) in 7 Easy Steps (+ Free Easy-To-Adjust Excel Workbook Example)

In this Excel Nested XLOOKUP (Dynamic Lookup Value) Tutorial, you learn how to create an Excel nested XLOOKUP (dynamic lookup value) formula, where the lookup value for one XLOOKUP depends on the result of another XLOOKUP.

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

This Excel Nested XLOOKUP (Dynamic Lookup Value) 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 nested XLOOKUP (dynamic lookup value) 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 Nested XLOOKUP (Dynamic Lookup Value) 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 nested XLOOKUP (dynamic lookup value) example workbook

Excel Nested XLOOKUP (Dynamic Lookup Value) Formula Template/Structure

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-nested-xlookup-value/
=XLOOKUP(XLOOKUP(LookupValue1,ColumnOrRowWhereYouDoFirstSearch,ColumnOrRowWithValueToReturnFirst),ColumnOrRowWhereYouDoSecondSearch,ColumnOrRowWithFinalValueToReturn)


Get immediate free access to the Excel nested XLOOKUP (dynamic lookup value) example workbook

Step-by-Step Process to Create an Excel Nested XLOOKUP (Dynamic Lookup Value) Formula

(1) Call the XLOOKUP function.

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

When you create a nested XLOOKUP (dynamic lookup value) formula, the lookup value for one (the dependent) XLOOKUP depends on the result of another (the independent) XLOOKUP. The XLOOKUP function you call in this step #1 (and whose arguments you specify in steps #2 to #4) is the independent XLOOKUP.

(2) Use the first argument of the XLOOKUP function (lookup_value) to specify the first lookup value.

The lookup value you specify in this step #2 is:

  • The lookup value for the independent XLOOKUP.
  • The value you search for in the first column (when doing a VLookup) or row (when doing an HLookup) you search in (and specify in step #3).
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-nested-xlookup-value/
=XLOOKUP(LookupValue1,...)

(3) Use the second argument of the XLOOKUP function (lookup_array) to specify the first lookup array.

The lookup array you specify in this step #3 is:

  • The lookup array for the independent XLOOKUP.
  • The column (when doing a VLookup) or row (when doing an HLookup) where you search for the lookup value (you specified in step #2).

As a general rule: Specify the lookup array as:

  • A single-column (1 column, several rows) cell range, when doing a VLookup; or
  • A single-row (1 row, several columns) cell range, when doing an HLookup.
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-nested-xlookup-value/
=XLOOKUP(LookupValue1,ColumnOrRowWhereYouDoFirstSearch,...)

(4) Use the third argument of the XLOOKUP function (return_array) to specify the first return array.

The return array you specify in this step #4 is:

  • The return array for the independent XLOOKUP.
  • The column (when doing a VLookup) or row (when doing an HLookup) with the first value to return. This value (returned by the independent XLOOKUP you created in steps #1 to #4) is (in turn) the lookup value for the dependent XLOOKUP (you create in steps #5 to #7).

Ensure the number of rows (when doing a VLookup) or columns (when doing an HLookup) of the return array (you specify in this step #4) is the same as that of the corresponding lookup array (you specified in step #3).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-nested-xlookup-value/
=XLOOKUP(LookupValue1,ColumnOrRowWhereYouDoFirstSearch,ColumnOrRowWithValueToReturnFirst)

(5) Call the XLOOKUP function (a second time) and set its first argument (lookup_value) to the independent XLOOKUP function you created in steps #1 to #4.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-nested-xlookup-value/
=XLOOKUP(XLOOKUP(LookupValue1,ColumnOrRowWhereYouDoFirstSearch,ColumnOrRowWithValueToReturnFirst),...)

When you create a nested XLOOKUP (dynamic lookup value) formula, the lookup value for one (the dependent) XLOOKUP depends on the result of another (the independent) XLOOKUP. The XLOOKUP function you call in this step #5 (and whose arguments you specify in steps #6 and #7) is the dependent XLOOKUP.

(6) Use the second argument of the XLOOKUP function (lookup_array) to specify the second lookup array.

The lookup array you specify in this step #6 is:

  • The lookup array for the dependent XLOOKUP.
  • The column (when doing a VLookup) or row (when doing an HLookup) where you search for the value returned by the independent XLOOKUP you created in steps #1 to #4.

As a general rule: When creating a nested XLOOKUP (dynamic lookup value) formula, specify the lookup array as:

  • A single-column (1 column, several rows) cell range, when doing a VLookup; or
  • A single-row (1 row, several columns) cell range, when doing an HLookup.
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-nested-xlookup-value/
=XLOOKUP(XLOOKUP(LookupValue1,ColumnOrRowWhereYouDoFirstSearch,ColumnOrRowWithValueToReturnFirst),ColumnOrRowWhereYouDoSecondSearch,...)

(7) Use the third argument of the XLOOKUP function (return_array) to specify the second return array.

The return array you specify in this step #7 is:

  • The return array for the dependent XLOOKUP.
  • The column (when doing a VLookup) or row (when doing an HLookup) with the value to return. This value (returned by the dependent XLOOKUP you created in steps #5 to #7) is the final value returned by the nested XLOOKUP (dynamic lookup value) formula.

Ensure the number of rows (when doing a VLookup) or columns (when doing an HLookup) of the return array (you specify in this step #7) is the same as that of the corresponding lookup array (you specified in step #6).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-nested-xlookup-value/
=XLOOKUP(XLOOKUP(LookupValue1,ColumnOrRowWhereYouDoFirstSearch,ColumnOrRowWithValueToReturnFirst),ColumnOrRowWhereYouDoSecondSearch,ColumnOrRowWithFinalValueToReturn)


Get immediate free access to the Excel nested XLOOKUP (dynamic lookup value) example workbook

How (and Why) the Nested XLOOKUP (Dynamic Lookup Value) Formula Works

Consider using a nested XLOOKUP (dynamic lookup value) formula when the lookup value for one (the dependent) XLOOKUP depends on the result of another (the independent) XLOOKUP.

The nested XLOOKUP (dynamic lookup value) formula template/structure you learned above:

  • Passes the result of one (the independent) XLOOKUP);
  • As lookup value for a second (dependent) XLOOKUP.


Get immediate free access to the Excel nested XLOOKUP (dynamic lookup value) example workbook

Excel Nested XLOOKUP (Dynamic Lookup Value) Example Formula

This Excel Nested XLOOKUP (Dynamic Lookup Value) 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 nested XLOOKUP (dynamic lookup value) example workbook

Excel Nested XLOOKUP (Dynamic Lookup Value) Example Worksheet

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

(1) Table 1 (cells A6 to B16).

The first table:

  • With data.
  • Where I search with the nested XLOOKUP (dynamic lookup value) example formula.

This is the table the independent XLOOKUP works with.

(2) Table 2 (cells A18 to H28).

The second table:

  • With the data.
  • Where I search with the nested XLOOKUP (dynamic lookup value) example formula.

This is the table the dependent XLOOKUP works with.

(3) First lookup value (cells J6 and K6).

The first lookup value (ID 058) is stored in cell K6.

(4) Nested XLOOKUP (dynamic lookup value) example formula (cells J8 to K9).

  • Cell J9:
    • Stores the nested XLOOKUP (dynamic lookup value) example formula; and
    • Displays the results.
  • Cell K9 displays the nested XLOOKUP (dynamic lookup value) example formula I enter in cell J9.
Example worksheet for Excel nested XLOOKUP (dynamic lookup value)

Excel Nested XLOOKUP (Dynamic Lookup Value) Example Formula

The nested XLOOKUP (dynamic lookup value) example formula stored in cell J9 is as follows:

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-nested-xlookup-value/
=XLOOKUP(XLOOKUP(K6,A7:A16,B7:B16),A19:A28,H19:H28)

The first lookup value (stored in cell K6) is “ID 058”.

The independent XLOOKUP:

  • Finds the first lookup value (ID 058) in row 13 (cell A13) in the first column I search in with the nested XLOOKUP (dynamic lookup value) example formula (cells A7 to A16); and
  • Returns the value stored in row 13 (Salesperson 36, in cell B13) of the applicable column (cells B7 to B16).

The dependent XLOOKUP:

  • Find the value returned by the independent XLOOKUP (Salesperson 36) in row 20 (cell A20) of the second column I search in with the nested XLOOKUP (dynamic lookup value) example formula (cells A19 to A28); and
  • Returns the value stored in row 20 (cell H20) of the applicable column (cells H19 to H28).
Excel nested XLOOKUP (dynamic lookup value) example formula


Get immediate free access to the Excel nested XLOOKUP (dynamic lookup value) example workbook

Download the Excel Nested XLOOKUP (Dynamic Lookup Value) Example Workbook

This Excel Nested XLOOKUP (Dynamic Lookup Value) 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 nested XLOOKUP (dynamic lookup value) 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 Nested XLOOKUP (Dynamic Lookup Value) 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 nested XLOOKUP (dynamic lookup value) 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