Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: Index Match Function For Two Same Values

  1. #1
    Join Date
    12th March 2007
    Posts
    128

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    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

  3. #3
    Join Date
    12th March 2007
    Posts
    128

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    Re: Index Match Function For Two Same Values

    Hi,

    See the attachment.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

  5. #5
    Join Date
    12th March 2007
    Posts
    128

    Re: Index Match Function For Two Same Values

    Nice, really very nice!
    Thanks

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Equivalent To “match”, And “index” Function In Access
    By jhonexcel in forum Excel and/or Access Help
    Replies: 11
    Last Post: May 28th, 2008, 20:53
  2. Index Match Function With Two Criteria
    By OnziTam in forum Excel General
    Replies: 7
    Last Post: January 25th, 2007, 21:03
  3. Replies: 10
    Last Post: April 21st, 2006, 05:46
  4. lookup, index, or match function?
    By cavhooah in forum Excel General
    Replies: 5
    Last Post: February 24th, 2006, 07:00
  5. Sum of two values from and INDEX MATCH
    By EmmaFairclough in forum Excel General
    Replies: 3
    Last Post: June 17th, 2004, 03:15

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
  •  
porno