 # Repeat All Products By Units Sold Frequency Distribution using LET | INDEX MATCH & XLOOKUP Functions

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

Posted in Blog.