+ Reply to Thread
Results 1 to 2 of 2

Pull 1st 2nd 3rd etc.. text from a date range by corresponding cells highest value

  1. #1
    Registered User
    Join Date
    10-21-2014
    Location
    usa
    MS-Off Ver
    excel 2013
    Posts
    4

    Pull 1st 2nd 3rd etc.. text from a date range by corresponding cells highest value

    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
    Last edited by ntc321; 04-02-2022 at 01:44 PM.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Pull 1st 2nd 3rd etc.. text from a date range by corresponding cells highest value

    Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] need to pull a max value based on highest quarter of a date range
    By cblouin in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-08-2020, 05:53 PM
  2. Replies: 5
    Last Post: 03-14-2019, 05:09 PM
  3. Replies: 6
    Last Post: 01-09-2019, 05:33 AM
  4. If all cells in range contain a date value, then return the highest date
    By jordan2322 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-02-2017, 08:12 PM
  5. [SOLVED] Formula to pull highest cell value from range
    By sryder in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-06-2016, 07:33 PM
  6. Replies: 5
    Last Post: 01-03-2012, 12:35 PM
  7. Based on date, pull sum from a range of cells?
    By infinitysales in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-23-2008, 06:08 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1