Results 1 to 2 of 2

Thread: Find max value and second largest in a row and return column header

  1. #1
    Junior Member
    Join Date
    Jun 2013
    Posts
    25
    Rep Power
    0

    Find max value and second largest in a row and return column header

    Sample data.xlsxI am using below formula to return column header in cell I2 and J2

    =INDEX($A$1:$H$1,MATCH(LARGE($A2:$H2,COLUMNS($I2:I 2)),$A2:$H2,0))

    Data is..

    US MX SAM EU AS CN AF Other
    3 1 1 0 1 12 0 0
    0 0 0 0 1 0 0 0
    0 0 0 0 0 2 0 0
    1 0 0 0 0 0 0 0
    2 0 0 0 0 0 0 0
    0 0 0 0 0 4 0 0
    0 0 1 0 0 1 0 0
    2 1 1 0 0 0 0 0
    1 0 0 0 1 12 0 0
    1 0 0 0 0 0 0 0
    2 0 0 0 1 12 0 0


    Now I had amended that data as follows...

    Client Name US MX SAM EU AS CN AF Other
    Microsoft 3 1 1 0 1 12 0 0
    Nokia 0 0 0 0 1 0 0 0
    Apple 0 16 0 9 5 2 0 0
    Sun 1 0 0 0 0 0 0 0
    Mitsubishi 2 0 0 9 0 0 18 0
    Honda 7 10 0 0 7 4 0 45
    Pepsico 0 6 1 7 0 1 0 0

    Expected result as follows....

    Result
    Client Name 1st Largest Value 2nd Largest Value
    Apple MX EU
    Honda Other MX
    Pepsico EU MX


    I am trying to apply the formula by lookup client name. Foreg: In modified data below, I have just added client name column. I have list of client names in other tab against which i need the data.
    How to amend the formula to fit in cells B14, C14, B15, C15, B16 and C16.
    Last edited by excel_1317; 09-18-2013 at 10:26 AM. Reason: formatting of data

  2. #2
    Junior Member
    Join Date
    Aug 2013
    Posts
    18
    Rep Power
    0
    Hi,

    This formula will give you 1st largest client name

    INDEX($A$1:$I$1,1,MATCH(MAX(OFFSET($A$1,MATCH(A14, $A$2:$A$8,0),0,1,9)),OFFSET($A$1,MATCH(A14,$A$2:$A $8,0),0,1,9),0))

Similar Threads

  1. Replies: 4
    Last Post: 04-05-2013, 12:08 PM
  2. Find Last Used Row In A Column Using Long Variable
    By xander1981 in forum Excel Help
    Replies: 2
    Last Post: 01-27-2013, 08:53 PM
  3. Find Last Used Row In Column
    By Rasm in forum Excel and VBA Tips and Tricks
    Replies: 2
    Last Post: 06-04-2012, 07:40 AM
  4. Find Value Based On MAX Value
    By Beaker Rex in forum Excel Help
    Replies: 3
    Last Post: 09-13-2011, 06:22 AM
  5. Find The Last Entry Row In A Column That Contains Only Numbers
    By Excel Fox in forum Excel and VBA Tips and Tricks
    Replies: 0
    Last Post: 05-09-2011, 05:19 AM

Posting Permissions

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