Nested XLOOKUP

Learn how to create a nested XLOOKUP formula if you need two lookup values to find the matching record in Excel.

We will show you how to use XLOOKUP to replace the INDEX and MATCH combination when you need a two-way lookup. For example, sometimes, we need two inputs to find the matching record in Excel.

Nested XLOOKUP: Using a two-way lookup

The two-way lookup formula enables you to find and extract the exact match. The point is that we use two variables in the nested XLOOKUP formula.

Before we take a deep dive, create three named ranges:

  • Period: C2:F2
  • Names: B3: F7
  • Sales: C3:F7

Generic formula to apply a two-way lookup using XLOOKUP:

=XLOOKUP(lookup value1, period, XLOOKUP(lookup value2, names, sales)

We want to find and get the sales based on two variables in the example. Our goal is to get the sales if the name is equal to Peter (lookup value1) and the period is equal to Q3 (lookup value2).

nested xlookup using two way lookup

To apply a two-way lookup, a nested XLOOKUP method. The formula in I4 is:

=XLOOKUP(I3,period,XLOOKUP(I2,names,sales))

XLOOKUP performs lookups in horizontal or vertical arrays and returns an entire row or column. We will use this ability in nested XLOOKUP formulas. The formula inside gets the lookup array, and the outer formula will use it as 3rd argument to return the correspondent sales.

Explanation

Let us evaluate the formula from the inside out! First, the inner XLOOKUP formula returns the array where we will find the sales for the given period.

=XLOOKUP(I2, names, sales)

The lookup value has in the second row so that the formula will return with the entire row. The result is an array that contains four periods of sales for Peter.

={16733, 40101, 36422, 25246}
inner XLOOKUP formula

We will use this “return array” as an input for the outer XLOOKUP; the formula will use this array as a third argument:

=XLOOKUP(I2, period, {16733, 40101, 36422, 25246})

Finally, the outer XLOOKUP formula finds the lookup value in the third period. The matching value is Q3 so the formula will get the third item in a range.

Alternatively, you can use the nested XLOOKUP without named ranges:

=XLOOKUP(I3, C2:F2, XLOOKUP(I2, B3: F7, C3:F7)

Two-way lookup: Workaround with INDEX and MATCH

If your Excel has no XLOOKUP support, you can perform a two-way lookup using the well-known INDEX and MATCH combination.

Formula:

=INDEX(C3:F7,MATCH(I2,names,0),MATCH(I3,C2:F2,0))
index and match performs two way lookup

Explanation:

=MATCH(I2,names,0) //2nd row
=MATCH(I3,period,0) //3rd column
=INDEX(C3:F7,2,3)

Nested XLOOKUP: Create the lookup value

We used the inner formula to get the lookup array in the demonstrated formula above. We aim to find the total cost in the second table where the Product is “Banana” in the first table. As usual, we will use named ranges in case of advanced formulas.

We need to find the link (Product Id) in the first table and then use it as a lookup value in the outer lookup formula.

find the lookup value for nested xlookup

First, use a left lookup in the first table to get the correspondent ID.

=XLOOKUP($E$3,fruits,products1)  //Product3

We will use the result –Product 3– as the lookup value for the main formula. Apply “Product 3” as a first argument to find the matching record in the second table:

=XLOOKUP(F3,products2,cost)

Replace F3 with the original formula; here is the nested XLOOKUP formula that supports two-way lookups:

=XLOOKUP(XLOOKUP($E$3,fruits,products1),products2,cost)

Related Formulas:

Istvan Vozar

Istvan is the co-founder of Visual Analytics. He helps people reach the top in Excel.