+ Reply to Thread
Results 1 to 6 of 6

Vlookup highest to lowest and return column name

  1. #1
    Registered User
    Join Date
    09-06-2016
    Location
    Oss, Netherlands
    MS-Off Ver
    2010
    Posts
    2

    Vlookup highest to lowest and return column name

    Dear Forum members,

    In Sheet 1:
    I have a list with customers (column A) with behind it the turnover within different product groups and the total turnover. Example:

    excel1.png

    In Sheet 2:
    I managed to sort the data from sheet 1 to get a "top 4" list with customers based on its total turnover from highest to lowest. Example:

    excel2.png

    Required:
    Now I want to know the 'Top 4' product groups which need to go behind the customers top 4 list. Example:

    excel3.png

    Hope someone is able to help me solving this issue. Thanks indvance.

  2. #2
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Vlookup highest to lowest and return column name

    Your data structure is pretty inefficient... You'd be better storing the data in a normalised form, then reporting it in a crosstab.

    However, see the attached for a worked example which does what you want, using the rather nasty formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Attached Files Attached Files
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Vlookup highest to lowest and return column name

    I would suggest a helper column for the MATCH($C11,$F$2:$F$5,0) section might be in order.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  4. #4
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Vlookup highest to lowest and return column name

    Quote Originally Posted by xlnitwit View Post
    I would suggest a helper column for the MATCH($C11,$F$2:$F$5,0) section might be in order.
    I wouldn't - I'd suggest storing the original data in a better structure, in the first place!!

  5. #5
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Vlookup highest to lowest and return column name

    Indeed but, given the solution you provided, I think the helper column is in order.

    I imagine we are all aware that realities of life often interfere with how things actually should be done.

  6. #6
    Registered User
    Join Date
    09-06-2016
    Location
    Oss, Netherlands
    MS-Off Ver
    2010
    Posts
    2

    Re: Vlookup highest to lowest and return column name

    Hi Olly,

    Thank you for your post. From your solution you are matching the total turnover per customer (....;MATCH($C11;..) to get the required data as a result. However it is possible that from all the customers some would have the same (total) turnover. Won't this result in incorrect output? Can I use 'Customer' instead (....;MATCH($B11;....) since this is a unique value?

    Thanks again.

    Update:
    I changed the MATCH lookup value to 'Customer' $B11 and altered the lookup array as well. Seems to work fine.
    Last edited by Stuono; 09-06-2016 at 08:08 AM. Reason: Update

+ 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] Re: Find lowest 5 numbers in column A with highest values in column B
    By jd16 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-05-2013, 02:08 AM
  2. Replies: 4
    Last Post: 10-06-2013, 10:40 AM
  3. Find lowest 5 numbers in column A with highest values in column B
    By dmccoy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-06-2013, 09:22 AM
  4. [SOLVED] Formula to find lowest/highest value in a column and return value to that cell
    By maryren in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-25-2013, 11:21 AM
  5. Return Highest & Lowest Value from Vlookup
    By ioswoody in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-13-2013, 01:05 AM
  6. [SOLVED] Highlight highest lowest value every nth row in a column
    By Cicada in forum Excel General
    Replies: 8
    Last Post: 02-04-2013, 02:22 PM
  7. Replies: 7
    Last Post: 03-14-2012, 12:28 PM
  8. Return highest/lowest 6 values from a list
    By thekingsoutlaw in forum Excel General
    Replies: 4
    Last Post: 01-21-2008, 04:36 PM

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