Welcome to Excel Jet Consult blog post. In this tutorial, we are going to learn how to,”Repeal All Products By Units Sold Frequency Distribution using LET | INDEX MATCH & XLOOKUP Functions.” Let’s dive in
Video
Sample Data
The screenshot below shows a simple dataset that is formatted as an Excel Tables using CTRL + T keyboard shortcut. We’ve got 4 Products and corresponding Quantity Sold. What we want to do is to repeat all the products based on the units sold frequency distribution.
LET and INDEX MATCH Functions Solution
Although, we can perform the operation without the need for the LET Function. However, we are going to use the LET Function to handle the cell references.
- To perform the first example: in cell D2, execute the formula: =LET(p,Table1[Products],q,Table1[Qty],v,$D$1:D1,result,IFNA(INDEX(p,MATCH(FALSE,COUNTIF(v,p)=q,0)),””),result)
- Click Enter and Copy Down the Formula
From the screenshot below, we can see that we’ve successfully repeated the product based on the number of the quantity sold
LET and XLOOKUP Functions Solution
In the second example, we are going to use the combination of the LET and the XLOOKUP Functions.
- To perform the second example: in cell E2, execute the formula: =LET(p,Table1[Products],q,Table1[Qty],v,$E$1:E1,result,XLOOKUP(FALSE,COUNTIF(v,p)=q,p,””),result)
- Click Enter and Copy Down the Formula
From the screenshot below, we can see that we’ve successfully repeated the product based on the number of the quantity sold
See you in the next tutorial