# Excel 2007 : INDEX LARGE with two conditions.

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

2. ## 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?

3. ## Re: INDEX LARGE with two conditions.

Originally Posted by jvelez198
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]

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

#### 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