Return to Excel Formulas List

Double (Nested) XLOOKUP – Dynamic Columns – Excel

Download Example Workbook

Download the example workbook

This tutorial will demonstrate how to perform a Double (Nested) XLOOKUP in Excel. If you don’t have access to XLOOKUP, instead you can perform a nested VLOOKUP.

Double(Nested) XLOOKUP main

In scenarios where one criterion of a lookup task is dependent on another lookup, we can use the Double (Nested) XLOOKUP to perform the nested lookups.

There are at least three arguments in XLOOKUP where we can input another XLOOKUP: the lookup value (1st argument), lookup array (2nd argument) and return array (3rd argument).

Let’s explore each of the cases.

 

XLOOKUP in Lookup Value

We can nest a XLOOKUP into the lookup_value of another XLOOKUP.

Double(Nested) XLOOKUP 01

You would do this when the lookup_value (e.g., Product ID) is dependent on another value (e.g., Product name).

Let’s walkthrough the formula:

First, we need to extract the required lookup value for the main XLOOKUP:

Double(Nested) XLOOKUP 2

 

The result of the 1st XLOOKUP is then fed to the main XLOOKUP to perform a lookup at the 2nd table:

Double(Nested) XLOOKUP 03

Combining these two lookup processes results to our original formula:

 

XLOOKUP in Lookup Array

Alternatively, we can use a nested XLOOKUP to return the lookup_array.

Double(Nested) XLOOKUP 04

One important property of the XLOOKUP Function is that it can return a 1D array (vertical or horizontal) if the given return array is in 2D (i.e., table). In this case, If the lookup array is a vertical list, then the XLOOKUP Function will return a row, and if the lookup array is a horizontal list, it will return a column.

Let’s breakdown and visualize the formula:

First, we need to perform a lookup in the headers (e.g., column (or row) headers) and return the corresponding column (or row):

Double(Nested) XLOOKUP 05

Since the return array is 2D (e.g., B3:C5) and the lookup array is a horizontal list (e.g., B2:C2), the XLOOKUP will return a row (e.g., C3:C5).

The output array from this XLOOKUP is then fed to the lookup array of our main XLOOKUP:

Double(Nested) XLOOKUP 06

Putting all of these together results to our original formula:

 

XLOOKUP in Return Array

Similarly, we can use a nested XLOOKUP to return a dynamic return_array.

Double(Nested) XLOOKUP 07

 

Let’s breakdown and visualize the formula:

We use the column headers as the lookup array for the first XLOOKUP:

Double(Nested) XLOOKUP 08

Just like in the previous section, the XLOOKUP will return a row once it finds a match from the column headers.

The resulting output array is used as the return array of our main XLOOKUP:

Double(Nested) XLOOKUP 09

 

Combining these two results to our original formula: