Welcome to Excel Jet Consult blog post. In this blog post, we will learn how to calculate and return the Top Botton Nth Products by Total using VSTACK LET SEQUENCE XLOOKUP and LARGE Functions. Let’s get started.
Video
Sample Data
The screenshot below shows the sample data with three columns. In addition, we’ve got a dropdown list data validation in cell I1 which will make the formula dynamic.
Formula
For this operation, we are going to use the following functions: VSTACK, LET, SEQUENCE, XLOOKUP and the LARGE Functions
- In cell E2, execute the formula:
=VSTACK(LET(TopBottomNth,SEQUENCE(I1),product,data[Product],total,data[Total],XLOOKUP(LARGE(total,TopBottomNth),total,product)),” “,LET(TopBottomNth,SEQUENCE(I1),product,data[Product],total,data[Total],XLOOKUP(SMALL(total,TopBottomNth),total,product)))
The above advanced formula shows how to use the VSTACK function to stack the top and bottom products by total based on the selection in cell I3 and
In cell F2, execute the formula:
- =XLOOKUP(E2#,data[Product],data[Total],” “)
The formula above shows how to use the XLOOKUP function to return the Total for the Top and Bottom Products based on the output in column E.
See you in the next video
Excel File Top & Bottom Nth Products by Total using LET SEQUENCE, VSTACK, XLOOKUP & LARGE Functions