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).
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}
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))
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.
First, use a left lookup in the first table to get the correspondent ID.
=XLOOKUP($E$3,fruits,products1) //Product3We 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:
