+ Reply to Thread
Results 1 to 14 of 14

Find MAX value and return corresponding value in column A

  1. #1
    Registered User
    Join Date
    04-21-2017
    Location
    Netherlands
    MS-Off Ver
    Professional Plus 2016
    Posts
    16

    Find MAX value and return corresponding value in column A

    Hi guys,

    I have a big pivot table and what I need is the following:

    For each value in column A I need to retrieve the value in row 4 that has the MAX value for that row. E.g. for row 5 the formula would scan the entire row, find the MAX value and derive the respective column and give me for that column the value in row 4 (= the output I need). I just can't wrap my head around how to do this. Could one of you guys help?

    I only got a screenshot as my sample file is too big to upload here (1.8MB).



    Regards,
    Attached Images Attached Images
    Last edited by Slic3d; 10-13-2017 at 08:33 AM.

  2. #2
    Registered User
    Join Date
    04-21-2017
    Location
    Netherlands
    MS-Off Ver
    Professional Plus 2016
    Posts
    16

    Re: Find MAX value and return corresponding value in column A

    I'm sorry, the title of the thread is off.

  3. #3
    Registered User
    Join Date
    04-21-2017
    Location
    Netherlands
    MS-Off Ver
    Professional Plus 2016
    Posts
    16

    Re: Find MAX value and return corresponding value in column A

    Does anyone have an idea?

  4. #4
    Registered User
    Join Date
    06-11-2010
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    43

    Re: Find MAX value and return corresponding value in column A

    Could you do a max outside the pivot table? If you have the pivot returning the max for each grouping (rows and columns) then you have the max for each column within the pivot. Insert a column next to your pivot on the right (make a new column A) and do a max formula where you reference the cells and not click on the pivot.

    I have attached an example. I hope this helps. It would help to reference what you want in a specific cell from what range.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    04-21-2017
    Location
    Netherlands
    MS-Off Ver
    Professional Plus 2016
    Posts
    16

    Re: Find MAX value and return corresponding value in column A

    This is great! Just a question; how would I retrieve the correct grouping? You have the right ones, but they are values. Is there a way to do this automatically?

    Thank you for your effort already!
    Last edited by AliGW; 10-15-2017 at 08:59 AM. Reason: Unnecessary quotation removed.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2019 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    32,788

    Re: Find MAX value and return corresponding value in column A

    Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

    For normal conversational replies, try using the QUICK REPLY box below.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  7. #7
    Registered User
    Join Date
    06-11-2010
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    43

    Re: Find MAX value and return corresponding value in column A

    I'm not sure what you mean. I did a quick example. I assume you have a spreadsheet with the above pictured pivot table. Does the Pivot table go all the way to column xfd? If not insert a column in a and move the pivot table over 1 column. Then use the formula from the first cell of data to the last cell not including any grand total. When you change your data set and refresh the pivot table the max will recalculate. Are you trying to move the max value somewhere else based on the value in column A in the picture? That is a separate requirement that is easily done. That can be a lookup or index/match formula.

  8. #8
    Registered User
    Join Date
    04-21-2017
    Location
    Netherlands
    MS-Off Ver
    Professional Plus 2016
    Posts
    16

    Re: Find MAX value and return corresponding value in column A

    Hi mstwntd,

    What I mean is that I do not need the max as a value, but I need the max as a means to find the value I need.. I need to find the max in each row, and find the corresponding value in your row 4 (1 / 2 / 3 / 4 / 5 / 6).

    So for your example:

    For row 'a' it would retrieve value '1', as the max 55 is in column '1'
    For row 'b' it would retrieve value '1 (or 2)', as the max 77 is in column '1 and 2'
    For row 'c' it would retrieve none
    For row 'd' it would retrieve value '4' as the max 33 is in column 3
    etc.

    Do you have any idea?

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2019 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    32,788

    Re: Find MAX value and return corresponding value in column A

    Try this (the double comma is intentional):

    =INDEX(K$4:P$4,,MATCH(MAX(K5:P5),K5:P5,0))

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    23,839

    Re: Find MAX value and return corresponding value in column A

    The lack of an example makes this a bit of a guess...

    An array formula, copied down:

    =IFERROR(MATCH(1/(1/MAX(IF(K5:P5>0,K5:P5))),K5:P5,0),"")

    Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

    You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly brackets yourself - it won't work...
    Glenn



  11. #11
    Registered User
    Join Date
    04-21-2017
    Location
    Netherlands
    MS-Off Ver
    Professional Plus 2016
    Posts
    16

    Re: Find MAX value and return corresponding value in column A

    THANK YOU AliGW! Works like a charm.

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2019 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    32,788

    Re: Find MAX value and return corresponding value in column A

    You're welcome!

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  13. #13
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    23,839

    Re: Find MAX value and return corresponding value in column A

    Does it work??? Doesn't it return a 3 when it should return a blank in the 3rd row??

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2019 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    32,788

    Re: Find MAX value and return corresponding value in column A

    Why should it return a blank? The maximum value in the row is a zero. We are using the attachment in post #4.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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