Hey all and TIA for any assistance,
Background:
- I have a sheet with all my master data
- On a separate sheet I have a formula that looks up the store, department, and unit sales. It returns the item # for the top 15 unit sales within that dept.
- The formula works GREAT as long as there are no duplicates.
- If there are duplicate values, it only returns the first one it comes to. So if Product A & Product B both have unit sales of 6, the formula will only list Product A. How do I alter the formula so that it lists Product A & Product B.
Here is my formula
=XLOOKUP(LARGE(IF('Next 3 Weeks'!$J:$J=K$42, 'Next 3 Weeks'!$H:$H), K43), IF('Next 3 Weeks'!$J:$J=K$42, 'Next 3 Weeks'!$H:$H), 'Next 3 Weeks'!$F:$F)
- 'Next 3 Weeks'!$J:$J is a column where I have combined the Store # & Dept #
- K$42 this is the cell that I manually type the store and Dept that I want to look at
- 'Next 3 Weeks'!$H:$H is the column that lists my unit sales
- K43 is the rank I want returned 1-15
- 'Next 3 Weeks'!$F:$F this is the column that lists the item numbers and what is listed based upon the rank in K43.
As you can see in the table below, this is what is returned. What do i add to my formula above so that it lists Product 15?
Thank you,
Background:
- I have a sheet with all my master data
- On a separate sheet I have a formula that looks up the store, department, and unit sales. It returns the item # for the top 15 unit sales within that dept.
- The formula works GREAT as long as there are no duplicates.
- If there are duplicate values, it only returns the first one it comes to. So if Product A & Product B both have unit sales of 6, the formula will only list Product A. How do I alter the formula so that it lists Product A & Product B.
Here is my formula
=XLOOKUP(LARGE(IF('Next 3 Weeks'!$J:$J=K$42, 'Next 3 Weeks'!$H:$H), K43), IF('Next 3 Weeks'!$J:$J=K$42, 'Next 3 Weeks'!$H:$H), 'Next 3 Weeks'!$F:$F)
- 'Next 3 Weeks'!$J:$J is a column where I have combined the Store # & Dept #
- K$42 this is the cell that I manually type the store and Dept that I want to look at
- 'Next 3 Weeks'!$H:$H is the column that lists my unit sales
- K43 is the rank I want returned 1-15
- 'Next 3 Weeks'!$F:$F this is the column that lists the item numbers and what is listed based upon the rank in K43.
As you can see in the table below, this is what is returned. What do i add to my formula above so that it lists Product 15?
Thank you,
| SKU | Unit Sales |
| Product 1 | 352 |
| Product 2 | 250 |
| Product 3 | 204 |
| Product 4 | 77 |
| Product 5 | 69 |
| Product 6 | 56 |
| Product 7 | 39 |
| Product 8 | 31 |
| Product 9 | 30 |
| Product 10 | 22 |
| Product 11 | 18 |
| Product 12 | 15 |
| Product 13 | 7 |
| Product 14 | 6 |
| Product 14 | 6 |
