Page 1 of 3 123 LastLast
Results 1 to 10 of 25

index match max!

This is a discussion on index match max! within the Excel Questions forums, part of the Question Forums category; =INDEX(A1:F2,MATCH(MAX(A2:F2),A2:F2,0),1) The desriptions are in a row in cells A through F The values are in the cells below I ...

  1. #1
    Board Regular justme's Avatar
    Join Date
    Aug 2002
    Location
    USA
    Posts
    698

    Default index match max!

    =INDEX(A1:F2,MATCH(MAX(A2:F2),A2:F2,0),1)


    The desriptions are in a row in cells A through F
    The values are in the cells below

    I want to find the max value and return the description above.

    I come up with the formula above, which is not giving me the correct answer. I get the answer of 5 as shown below.

    A B C D E F
    5 8 7 1 3 5


    5
    Excel 2007/Windows 7

  2. #2
    VoG
    VoG is offline
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    62,674

    Default Re: index match max!

    Is it?

    Sheet7

     ABCDEFGH
    1ABCDEF B
    2587135  

    Spreadsheet Formulas
    CellFormula
    H1=INDEX(A1:F1,MATCH(MAX(A2:F2),A2:F2,0))

    Excel tables to the web - Excel Jeanie Html 4
    HTH, Peter
    Please test any code on a copy of your workbook.

  3. #3
    Board Regular btadams's Avatar
    Join Date
    Jan 2003
    Location
    Richmond, Va
    Posts
    1,871

    Default Re: index match max!

    try this:

    =INDEX(A1:F1,MATCH(MAX(A2:F2),A2:F2,0))
    “The early bird gets the worm, but the second mouse gets the cheese.”

    Put Yourself on the Excel Map:
    http://www.mapservices.org/myguestmap/map/MrExcel

    Instructions for Map:
    http://www.mrexcel.com/map.html

  4. #4
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    32,297

    Default Re: index match max!

    You've got the row# and column# reversed in the Index..
    You have
    =INDEX(Range,Column#,Row#)

    But it should be
    =INDEX(Range,Row#,Column#)

    Try
    =INDEX(A1:F2,1,MATCH(MAX(A2:F2),A2:F2,0))

    or simpler, your range does not need to be the ENTIRE 2 row 6 column Range. You can just put the row 1. Then you don't have to specify the Row#, it's smart enough to figure it out.

    =INDEX(A1:F1,MATCH(MAX(A2:F2),A2:F2,0))
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  5. #5
    Board Regular justme's Avatar
    Join Date
    Aug 2002
    Location
    USA
    Posts
    698

    Default Re: index match max!

    Thank you gentlemen! I knew I was close.
    Excel 2007/Windows 7

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    64,239

    Default Re: index match max!

    Quote Originally Posted by justme View Post
    =INDEX(A1:F2,MATCH(MAX(A2:F2),A2:F2,0),1)


    The desriptions are in a row in cells A through F
    The values are in the cells below

    I want to find the max value and return the description above.

    I come up with the formula above, which is not giving me the correct answer. I get the answer of 5 as shown below.

    A B C D E F
    5 8 7 1 3 5


    5
    ABCDEF
    587185
    Max
    8
    Count
    2
    List
    B
    E


    A1:F2 houses the data, headers and values...

    A6:

    =MAX(A2:F2)

    A8:

    =COUNTIF(A2:F2,A6)

    A10:

    Control+shift+enter, not just enter...
    Code:
    =IF(ROWS($A$10:A10)<=$A$8,INDEX($A$1:$F$1,
       SMALL(IF($A$2:$F$2=$A$6,COLUMN($A$1:$F$1)-COLUMN($A$1)+1),
         ROWS($A$10:A10))),"")
    and copy down.

  7. #7
    Board Regular justme's Avatar
    Join Date
    Aug 2002
    Location
    USA
    Posts
    698

    Default Re: index match max!

    If my information is found in multiple worksheets and I name some ranges. My test example has descriptions in cell C1 and Values in C2. I've named the ranges in Sheets 1-4. Descriptions= first:last!$C1, CValues = first:last!$C2

    I am able to find: Max(CValues).

    Now I want to find the description for the Max(CValues)

    I've tried: =INDEX(Descriptions,MATCH(MAX(cvalues),cvalues,0)). This returns a #value!error.

    I've also referenced the cell where I tested to see if it would find the max value over multiple worksheets. In cell A4 I have entered: max(cvalues) edit: (This returns the #Value! error)

    Then my equation became: =index(descriptions,match(a4),cvalues,0))

    This returns the error that I've entered too few arguemens for this function.

    Likewise with the lookup function: =LOOKUP(MAX(cvalues),cvalues,Descriptions)
    Last edited by justme; May 14th, 2009 at 08:11 AM. Reason: added error code
    Excel 2007/Windows 7

  8. #8
    Board Regular justme's Avatar
    Join Date
    Aug 2002
    Location
    USA
    Posts
    698

    Default Re: index match max!

    =HLOOKUP(MAX(cvalues),first:last!C1:C2,-1,TRUE) Also returns a value error

    Ideas?
    Excel 2007/Windows 7

  9. #9
    Board Regular btadams's Avatar
    Join Date
    Jan 2003
    Location
    Richmond, Va
    Posts
    1,871

    Default Re: index match max!

    Using the example in your first post,

    =INDEX(A1:F1,MATCH(MAX(A2:F2),A2:F2,0))

    if you name A1:F1 "Descriptions" and A2:F2 "CValues" then the formula

    =INDEX(Descriptions,MATCH(MAX(cvalues),cvalues,0))

    should work.
    “The early bird gets the worm, but the second mouse gets the cheese.”

    Put Yourself on the Excel Map:
    http://www.mapservices.org/myguestmap/map/MrExcel

    Instructions for Map:
    http://www.mrexcel.com/map.html

  10. #10
    Board Regular justme's Avatar
    Join Date
    Aug 2002
    Location
    USA
    Posts
    698

    Default Re: index match max!

    Good morning. That is exactly what I thought. However, in post #7 that is exactly what I tried to do and it returns a #VALUE! error.
    Excel 2007/Windows 7

Page 1 of 3 123 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com