+ Reply to Thread
Results 1 to 3 of 3

Return multiple MAX values and account for duplicates

  1. #1
    Registered User
    Join Date
    06-22-2017
    Location
    CT
    MS-Off Ver
    2016
    Posts
    16

    Return multiple MAX values and account for duplicates

    I am looking for a formula that searches a range for MAX or MIN value(s) and returns the corresponding column header(s). I can write the formula in such a way that it returns one answer (the first MAX/MIN in the range), but I want it to return ALL the MAXs/MINs, not just the first one. I would like the returns to be in one cell, whether or not they're concatenated with a [comma] or just put together [Q1Q2].

    I've embedded a photo for reference but I've also attached a sheet.

    Capture6.JPG

    N6/O6 is looking for the MAX/MIN from AJ18:AN18, respectively, and returning the respective column header from AJ3:AJ18. P6/Q6 is returning the MIN/MAX from AO18:AS18, respectively, and returning the appropriate column header from AO3:AS3.

    Problem is, as you can see, AJ18:AN18 has multiple max values (2@22), but my formula in N6 (=INDEX($AJ$3:$AN$3,0,MATCH(MAX($AJ$18:$AN$18),$AJ$18:$AN$18,0)) only returns the header of the first 22 (Q2). I'd like a formula that returns EACH of the MAX/MIN value headers. The appropriate return in this case would be: Q2/Q3 or Q2Q3, something like that.

    There may be instances where there is no duplicate, in which case the formula should be able to account for that.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Return multiple MAX values and account for duplicates

    Hi
    Basically,this will cover 3 similar numbers that are considered maximum in the range.

    Please Login or Register  to view this content.
    This is for the MIN:

    Please Login or Register  to view this content.
    Last edited by Limor_OP; 09-21-2020 at 06:06 PM.

  3. #3
    Registered User
    Join Date
    06-22-2017
    Location
    CT
    MS-Off Ver
    2016
    Posts
    16

    Re: Return multiple MAX values and account for duplicates

    So far so good. Thanks!

+ 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] Return values based on multiple criteria with no duplicates
    By Ksun23 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-20-2018, 08:34 PM
  2. Replies: 2
    Last Post: 03-27-2017, 05:35 PM
  3. [SOLVED] ARRAY/PIVOT + duplicates - need to return multiple match values
    By swinksel in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-05-2016, 05:08 PM
  4. Return values of duplicates
    By kneenah in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-22-2014, 05:19 PM
  5. if Account number = return Account Name
    By mom2carisa in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 09-04-2013, 06:03 PM
  6. Return Account Nickname if Account Number Matches
    By spacle in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-03-2013, 11:04 AM
  7. return multiple values horizontally while removing duplicates
    By kingi in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-24-2013, 03:55 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