League Table - VLOOKUP

[Clare]

Hello - newbie poster here.

I've been scouring this group during my current project and found much
useful information - thanks to all. However I've come to a halt and
need to ask for help on a specific question.

I have a spreadsheet working out the Rugby Zurich Premiership scores
(LOTS of formulae entering!). The ultimate is to display an
automatically calculating league table from the datasheet. I've used
the LARGE function to order the points for each team and VLOOKUP to
pull in the corresponding team name, games won, lost, drawn, points
for and against and the goal difference. My problem is that two teams
have the same points and Excel returns the same team name and details
twice rather than first one team then the other. I've read and
re-read info on MATCH, INDEX, IF and pivot tables, none of which make
any sense (maybe I've been staring at it too long!).

I'm sure there's a way to ask Excel to look at another column of
criteria (games won, then goal difference) if the Pts are the same and
order the two teams accordingly, but the answer is eluding me.

Does anyone have any ideas or can point me to the correct command to
use please?

Thanks for any help in advance.

Clare

 

[dizzykid]

Clare,
I made a similar project once (10 hockey teams), and solved the problem by
combining several cells into a composite key of sorts and using it instead
of points. The first step was to make a summary table of the league with
teams occupying rows 5-14. The teams' stats were drawn from other sheets.
The next step was to create a second table below the first (later hidden),
with teams occupying rows 20-29 and using LARGE to sort them.

Assume the columns: D=Team Name, F=Games Played, G=Wins, H=Losses, I=Ties,
J=OT Losses, K=Points, L=Goals For-Against. (Both tables) Our league's
system was to favour the team with more wins. If the tie remained, the team
with a higher Goals Differential (For-Against) would be considered ahead.

In cell C5 of the first table, I put =$K5*1000000+$G5*1000+(500+$L5) The
math here is simply to keep the stats neatly in their own decimal positions.
You may have to adjust depending on the number of games in your season.
In C20 of the second table, =LARGE($C$5:$C$14,ROW(C20)-ROW(C$19))
In D20 of the second table,
=VLOOKUP($C20,$C$5:$N$14,COLUMN(D20)-COLUMN($C20)+1,FALSE)
Both cells are then copied down 10 rows, then D20-D29 can be copied across
to the right to fill out the stats.

Once the second table is functioning well, the first one can simply be
hidden. I hope this is of some use to you.
Chris

 

[Clare]

YIPPEEEE!!! Thanks so much Chris...I used the first part of your
recommendation and in the first (hidden) table, each team now has it's
own unique identifying number based on the pts, games won and goal
difference as you suggested. I then used LARGE to get these numbers
to order in a hidden column, then vlookedup corresponding figures from
table 1 into table 2 which is the visible, automatically updating
league table. Fantastic!

Thanks very much for taking the time to explain how you did it....I
really appreciate it.

Clare

PS: If you or anyone else wants a fully operational Zurich Rugby
Premiership League 2003/4 table (including all the bonus point
calculations!!) - email me and I'll happily forward a copy.