Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Index Match Function For Two Same Values

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Index Match Function For Two Same Values

    I am using Index & Match function to get the desired values where in from a table Those having largest percentage gain/loss is extracted

    suppose the data are as follows
    SCRIP A 3.2
    SCRIP B 2.55
    SCRIP C 0.35
    SCRIP D 1.1
    SCRIP E 0
    SCRIP F 2.95
    SCRIP G 0.5
    SCRIP H 1.05
    SCRIP I 0.45
    SCRIP J 2.55
    SCRIP K 3.8
    SCRIP L 2.9

    The result will be
    SCRIP K 3.8
    SCRIP A 3.2
    SCRIP F 2.95
    SCRIP L 2.9
    SCRIP B 2.55
    SCRIP B 2.55

    instead of
    SCRIP K 3.8
    SCRIP A 3.2
    SCRIP F 2.95
    SCRIP L 2.9
    SCRIP B 2.55
    SCRIP J 2.55

    Why? what I am gusseing is that the formula is taking the result on ascenting order basis. Is this the limitation of Index/Match, if yes, then what can I do to get the correct result

    The forn=mula i am using for getting the scrip name
    =INDEX(Summary!$B$2:$I$13,MATCH(B3,Summary!$I$2:$I$13,0),1)
    where "Summary" is the name of the sheet

  • #2
    Re: Index Match Function For Two Same Values

    Hi,

    Try,

    In J2 on Summary and copied down,

    =RANK(I2,$I$2:$I$13)+COUNTIF($I$2:I2,I2)-1

    Now your formula will be..

    =INDEX(Summary!B$2:B$13,MATCH(RANK($B3,$B$3:$B$8)+COUNTIF($B$3:$B3,$B3)-1,Summary!$J$2:$J$13,0))

    HTH
    Kris

    ExcelFox

    Comment


    • #3
      Re: Index Match Function For Two Same Values

      The solution is not solving the problem
      Sorry that the data was not showing properly in the post and as the file was showing big size even after zipping, so I could not att the file also. Now I have extracted some part and posting the file. Help this will clear my doubt & qwery.
      Attached Files

      Comment


      • #4
        Re: Index Match Function For Two Same Values

        Hi,

        See the attachment.
        Attached Files
        Kris

        ExcelFox

        Comment


        • #5
          Re: Index Match Function For Two Same Values

          Nice, really very nice!
          Thanks

          Comment

          Trending

          Collapse

          There are no results that meet this criteria.

          Working...
          X