I am trying to pull the top ten selling products by the amount sold. I used a mixture of query, filter, and large to pull the highest value in a range plus qtys sold to match. However, I'm using vlookup to pull the product name over. I was able to have the vlookup filter range by date so its pulling the right date range. The problem here is that I have multiple values that are the same looking at the vlook up range. I need to pull the 2nd or 3rd value dynamically if one is available.
It won't let me post a google doc to share my formulas. but it looks something like this. date 2 is just datevalue formating and the line item is in there twice because of vlookup to pull the last value. Also, line item name starts at column d.
[Lineitem name] [SUM of Total] [SUM of Lineitem quantity] [Date 1] [date 2] [Line item Total] [Lineitem name]
Formula for pulling max sales "=large(query(filter($D:$I,$H:$H>=$N$19,$H:$H<=$M$19),"select Col6"),1)"
Formula for pulling max qty "=LARGE(query(filter($D:$I,$H:$H>=$N$19,$H:$H<=$M$19),"select Col3"),1)"
Formula for vlookup "=vlookup(large(query(filter($D:$I,$H:$H>=$N$19,$H:$H<=$M$19),"select Col6"),1),FILTER(I:J,$H:$H>=$N$19,$H:$H<=$M$19),2,0)"
The vlookup works unless there is a duplicate value then it just pulls the duplicate value.
Expected results
[Top Ten Products MTD]
Super Novo Massage Chair by Human Touch® - Espresso
Novo XT2 Massage Chair by Human Touch® - Red
Hale AirComfort Zero Gravity Recliner with Air Massage - Saddle / Honeywood
Gravis Zero Gravity Recliner with Air Massage by Human Touch® - Black / Beech
Gravis Zero Gravity Recliner with Air Massage by Human Touch® - Bone / Mahogany
Gravis Zero Gravity Recliner with Air Massage by Human Touch® - Sand / Beech
Perfect Chair® Zero Gravity Omni-Motion Classic Power Recliner - Premium Leather / Oak / Supreme
Perfect Chair® Zero Gravity Omni-Motion Classic Power Recliner - Premium Leather / Black / Supreme
Sunset Lift Zero Gravity Chair by Relax The Back - Brisa Faux Leather / Coffee Bean
Perfect Chair® Zero Gravity Omni-Motion Silhouette Power Recliner - Premium Leather / Oak / Performance
Actual results
[Top Ten Products MTD]
Super Novo Massage Chair by Human Touch® - Espresso
Novo XT2 Massage Chair by Human Touch® - Red
Hale AirComfort Zero Gravity Recliner with Air Massage - Saddle / Honeywood
Gravis Zero Gravity Recliner with Air Massage by Human Touch® - Black / Beech
Gravis Zero Gravity Recliner with Air Massage by Human Touch® - Bone / Mahogany
Gravis Zero Gravity Recliner with Air Massage by Human Touch® - Bone / Mahogany
Perfect Chair® Zero Gravity Omni-Motion Classic Power Recliner - Premium Leather / Oak / Supreme
Perfect Chair® Zero Gravity Omni-Motion Classic Power Recliner - Premium Leather / Oak / Supreme
Sunset Lift Zero Gravity Chair by Relax The Back - Brisa Faux Leather / Coffee Bean
Sunset Lift Zero Gravity Chair by Relax The Back - Brisa Faux Leather / Coffee Bean
Bookmarks