Welcome to Excel Jet Consult blog post. In this post, we will consider how to leverage the XLOOKUP function combined with LET function to dynamically retrieve data. Let’s get started.
In this examples, we’ve got data showing Month and Units in columns A and B respectively as seen in the screenshot below
Our goal is to use nested XLOOKUP function to return the Units sold based on the selected Month in cells D3 and E3. Note, we’ve got list data validation in cell D3 and E3.
Nested INDEX/MATCH Functions
While we can achieve the same result using combination of INDEX MATCH with LET as seen in the screenshot below, we will focus on using the new XLOOKUP function.
Nested XLOOKUP
In cell D4, execute this formula:
=LET(a,XLOOKUP(D3,A4:A15,B4:B15): XLOOKUP(E3,A4:A15,B4:B15), rowSum,VSTACK(a,SUM(a)),rowSum)
Click Enter.
Formula Explanation
First, I defined a variable with letter (a) using the LET function which comprises the result of two XLOOKUP functions. The first XLOOKUP looks up the value in cell D3 within the range A4:A15 and returns the corresponding value from B4:B15. The second XLOOKUP does the same for the value in cell E3. The colon (:) between them was used to create a range of values, essentially capturing the results of both lookups.
Secondly, I defined another variable using (rowSum). The variable is defined using the LET function as the result of the VSTACK function applied to variable a and the SUM function. The VSTACK function stacks the values in a vertically, and SUM(a) calculates the sum of all the values in the array. This creates a new array with the original values followed by their sum.
Finally, I called the rowSum variable which is the stacked array of values from a and their sum as seen in the screenshot below.
From the above screenshot, the formula returned all the Units sold between January and May and in the last row, the rowSum variable used with VSTACK and SUM function returned the sum of the Units between Jan and May.
Switching to the month of Jan and Mar, the formula worked dynamically and we have the three rows of Units sold between month Jan to March and the sum of the three Month return 113
In summary, this formula performs a dynamic lookup using XLOOKUP for values in cells D3 and E3 within the specified range (A4:A15), stores the results in a variable a, and then creates a new array (rowSum) by stacking the original values with their sum. The final result is this new array.