Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    excel formula (office xp)

    Hi. [img]/forums/images/smilies/smile.gif[/img]. i was trying to do this formula (difficult for me) but couldn't. just find the max value of an an array and return the 'stuff' next to the cell of max value. as example, please see small sample in attached file. Thanks.

  2. #2
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: excel formula (office xp)

    Hi

    In your example you could use:
    <code>=VLOOKUP(F2,D1:E15,2,FALSE)</code>

    The first part (F2) is the value to lookup
    D1:E15 is the table that it is looking up, the first column is the column it looks in to match the value
    2 - this is the relative column in the range D1:E15 that it is looking in to get the result
    FALSE - this means it must find an exact match

  3. #3
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: excel formula (office xp)

    Hi Tony, thanks a lot. it works and you made my task lighter.

    Jill.

  4. #4
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: excel formula (office xp)

    What you want is a Top 1 (Max) list. What follows is a formula system I devised to create the so-called Top N lists.

    Let D1:E16 house the sample your attachment shows, which is extended with an additional record:

    {"NUMBERS","LETTERS";5,"b";9,"c";10,"d";2,"e";4,"f ";6,"g";7,"h";11,"i";20,"j";20,"k";25,"l";12,"m";3 ,"n";1,"o";25,"p"}

    In F1 enter: RANK

    In F2 enter & copy down:

    =RANK(D2,$D$2:$D$16)+COUNTIF($D$22,D2)-1

    In H1 enter: MAX VALUE

    In H2 enter:

    =MAX(D216)

    In H3 enter: 1 (which means: I want a Top 1 list.)

    In H4 enter:

    =MAX(IF(INDEX(D216,MATCH(H3,F2:F16,0))=D216,F2:F16))-H3

    which must be confirmed with control+shift+enter instead of just with enter.

    This formula calculates the number of the ties of the max value.

    In H5 enter & cpoy down:

    =IF(ROW()-ROW(H$5)+1<=$H$3+$H$4,INDEX($E$2:$E$16,MATCH(ROW()-ROW(H$5)+1,$F$2:$F$16,0)),"")

    The result list is:

    {"l";"p"}

    Running a pivot table on the sample would give you exactly the same list.
    Microsoft MVP - Excel

  5. #5
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: excel formula (office xp)

    =VLOOKUP(MAX(D215),D2:E15,2, FALSE)
    does it in one step

  6. #6
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: excel formula (office xp)

    Why don't you try it on the sample I used?
    Microsoft MVP - Excel

Posting Permissions

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