+ Reply to Thread
Results 1 to 6 of 6

query regarding index and match

  1. #1
    Registered User
    Join Date
    08-02-2011
    Location
    muscat, oman
    MS-Off Ver
    Excel 2003
    Posts
    5

    query regarding index and match

    Dear All:

    I have attached an excel file for reference.

    I have a data set of stock names and price and change in prices which updates itself everyday and need top 5 gainers of the day.

    steps i did:
    1.ranked them by rank formula.
    2. applied index and match formula to pick the names of and prices of those stocks based on the ranks.

    it seem to be working fine when all stocks % change in prices are different, but, when some stocks increase by same %, it gives them the same rank then my table doesnt work properly as it will display only 1 from the rank.

    I want it to show all the companies in that rank.

    I have attached a file to illustrate the issue.

    plus any suggestions if there is a more efficient way of doing this.
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: query regarding index and match

    You can change the formula in F7 to this:

    =RANK(D7,$D$7:$D$14)+COUNTIF($D$7:$D7,D7)-1

    to give each a unique ranking. Then your top-5 table will work.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    08-02-2011
    Location
    muscat, oman
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: query regarding index and match

    Thanks Pete.. Awesome...

    It works..

    but i dont understand what this COUNTIF($D$7:$D7,D7)-1 is doing exactly, can u explain a bit..

    Thanks alot

    Saad

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: query regarding index and match

    Notice that the range in the COUNTIF is anchored at D7, but the second element of the range is not anchored, so as it is copied down the range varies. It counts how many times D7 occurs in the range D7:D7 (obviously once) and then subtracts 1 from it to leave the RANK part of the formula unchanged.

    When it gets to F9 (the first time that D7 is 0%) that part of the formula is now:

    COUNTIF($D$7:$D9,D9)-1

    and again this results in zero so the RANK is unchanged.

    However, at F12 it becomes:

    COUNTIF($D$7:$D12,D12)-1

    and now it counts that there are two 0% values in the range D7:D12, and so this expression results in 1, to be added on to the RANK.

    In the next cell the expression results in 2 added on to the RANK value, and so on.

    Hope this helps.


    Pete

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: query regarding index and match

    If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

    Also, since you are relatively new to the forum (in terms of posts) you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    Pete

  6. #6
    Registered User
    Join Date
    08-02-2011
    Location
    muscat, oman
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: query regarding index and match

    Thanks a bunch Pete.. got your explanation aswell..

    I will mark it as resolved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] INDEX MATCH MAX Query
    By taylor_ in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-20-2017, 11:12 AM
  2. Index and three match query
    By ajayd3v in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-28-2013, 11:11 AM
  3. Index match query
    By jocer in forum Excel General
    Replies: 3
    Last Post: 07-08-2012, 07:17 AM
  4. Index Match Query
    By leekenkeong in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-03-2012, 10:16 AM
  5. Index & Match query
    By WLM1976 in forum Excel General
    Replies: 12
    Last Post: 12-07-2006, 10:53 AM
  6. index and match query
    By kate in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-06-2006, 08:00 PM
  7. INDEX AND MATCH QUERY ?
    By kate in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-17-2006, 08:25 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1