+ Reply to Thread
Results 1 to 3 of 3

Excel 2007 : INDEX LARGE with two conditions.

  1. #1
    Registered User
    Join Date
    02-18-2012
    Location
    Pensacola, Florida
    MS-Off Ver
    Excel 2007
    Posts
    1

    INDEX LARGE with two conditions.

    Q __________________ T___________________ U________________V

    GD________________Points(ref)____________ Team(Ref)________Team Final Position

    2____________________6__________________ESP_______________ING
    6____________________7__________________ING_______________ESP
    1____________________4__________________ITA_______________ ITA
    -9___________________0__________________HOL_______________HOL

    The Data Above is for a soccer pool bracket tracker that I am building.
    What I need is the following: I want column V to show the teams in column U, ordered by final position according to the number of points they have in column T.
    I used this formula to look for the team with more points and it seemed to work. =INDEX(T2:U8,MATCH(LARGE(T2:T8,1),T2:T8,0),2)
    Then I simply changed the condition to get the second highest and so on....
    However, when there are two teams with the same number of points, the formula stops working.
    My questions is: When two teams have the same number of points, how can I make the formula to evaluate column Q which shows GOAL Difference.
    For Example: IF ESP and ING had 3 points each, then I want the formula to look for their GD which according to column Q is ESP=2 and ING=6. this would result in ING being first.

    I hope someone can help me.

    thanks,

    Juan Velez
    Last edited by jvelez198; 02-18-2012 at 02:31 PM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,303

    Re: INDEX LARGE with two conditions.

    Possibly use a helper column that compares points? If the points are different, it returns points, if they are the same, it returns your alternate value. Then run your formula on that data instead?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Contributor
    Join Date
    10-28-2011
    Location
    Delhi
    MS-Off Ver
    Excel 2007
    Posts
    101

    Re: INDEX LARGE with two conditions.

    Quote Originally Posted by jvelez198 View Post
    Q __________________ T___________________ U________________V

    GD________________Points(ref)____________ Team(Ref)________Team Final Position

    2____________________6__________________ESP_______________ING
    6____________________7__________________ING_______________ESP
    1____________________4__________________ITA_______________ ITA
    -9___________________0__________________HOL_______________HOL

    The Data Above is for a soccer pool bracket tracker that I am building.
    What I need is the following: I want column V to show the teams in column U, ordered by final position according to the number of points they have in column T.
    I used this formula to look for the team with more points and it seemed to work. =INDEX(T2:U8,MATCH(LARGE(T2:T8,1),T2:T8,0),2)
    Then I simply changed the condition to get the second highest and so on....
    However, when there are two teams with the same number of points, the formula stops working.
    My questions is: When two teams have the same number of points, how can I make the formula to evaluate column Q which shows GOAL Difference.
    For Example: IF ESP and ING had 3 points each, then I want the formula to look for their GD which according to column Q is ESP=2 and ING=6. this would result in ING being first.

    I hope someone can help me.

    thanks,

    Juan Velez
    I have a little help for you...

    First sort the column Points (Ref) Largest to Smallest....and then use (Assuming table starst from A1)

    =IF((COUNTIF($B$1:B1,B2)=0)=TRUE,B2,"")

    You'll get Blanks against all duplicate values in Column B.

    [I also want to know the formula to solve the remaining issue]

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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