Trying to Find The Proper function

[Sean Barrie]

What function should i use for standings.

I have two tables on my spreedsheet, 1 shows the rosters and points the team
generated and the second looks like this:

Place Team Points
1 John Smith 60
2 Jane Smith 50
3 Jeff Smith 50
etc etc.

i have the totals from table 1 linked to the 2nd table using the "Large"
function. im trying to make table 2 100% dependable from the numbers from
table 1.

My questions for you guys is how do i link the team name to the teams total
points

i.e. if Jane smith gains 15 points to take the lead the table will look like
this on my speedsheet

Place Team Points
1 John Smith 65
2 Jane Smith 60
3 Jeff Smith 50

i need to know what function to use so that table looks like this
automatically

Place Team Points
1 Jane Smith 65
2 John Smith 60
3 Jeff Smith 50

 

[Pete_UK]

You use an INDEX/MATCH combination to do this. The MATCH function
looks at the points in the second table and finds the matching row in
the totals in table 1. This value is used within the INDEX function to
return the name which is on that row.

A word of caution, though - MATCH finds the first match, so if you
have two people with the same number of points then you need to cope
with this situation by means of some tie-break.

Hope this helps.

Pete

 

[Max]

Here's a simple formulas play which should get you going ..

Source table assumed in cols A to D, data from row2 down,
where the key col (Points) is col D

In F2: =IF(D2="","",D2-ROW()/10^10)
Leave F1 empty

In G2:
=IF(ROWS($1:1)>COUNT($F:$F),"",INDEX(B:B,MATCH(LARGE($F:$F,ROWS($1:1)),$F:$F,0)))
Copy G2 to I2. Select F2:I2, copy down to cover the max expected extent of
source data. Minimize/hide col F. Cols G to I will return the desired
auto-sorted list in descending order. Lines with tied points, if any, will be
returned in the same relative order that they appear within the source.

 

[Sean Barrie]

ok so i tried to play around with the match and index functions you said, but
i have made no progress.

so heres the layout if my two tables.

Table 1:

Row 1 Has all the team names (4) merged and centre'd from cells "a" to "y"
(B to G for one team H to M for another team etc etc)
Cells g17, m17, s17, y17 have their total points

The Second Table:

A20 - A23 show the standings number (1st, 2nd, etc)
B20 - B23 is supposed to show the team name
C20 - C23 has the totals from the cells G17, M17, S17 & Y17 listed largest
to smallest using the large function.

so how would i use the two functions to make it so that team name who has
the most points automatically appear in cell B20 the 2nd place team name
appear in Cell B21 Etc.

hopefully this helps better...