VLOOKUP table sort

Question

Is it possible to use a Vlookup function to sort a table in order. Take for example a basic sports league

Column A is their position (using a rank function to determine this)

Column B is the team name

Column C is their points (these points are automatically updated from another worksheet)

eg

2        Team A         30

3        Team B         28

3        Team C         28

1        Team D         35

5        Team E          20

Therefore the Vlookup table placing them in order: Team D, A, B, C, E

Answer 1

... Therefore the Vlookup table placing them in order: Team D, A, B, C, E 

Using SMALL() to sort the Position, then two INDEX(MATCH()) array formulas to retrieve the Team and Points will produce the results you are looking for. The array formulas formulas are necessary to compensate for duplicate  Position numbers.

                     

The standard formula in E3 is,

=SMALL($A$3:$A$7,ROWS($1:1))

... and the array formula in F3 is,

=INDEX(B$3:B$99,SMALL(IF($A$3:$A$99=$E3,ROW($A$3:$A$99)-ROW(E$3)+1),COUNTIF($E$3:$E3,$E3)))

... which requires Ctrl+Shift+Enter↵ rather than simply Enter↵. Once entered correctly, it can be filled right to G3 then E3:G3 can be filled down as necessary to catch all values from the unsorted table data.

I've uploaded that sample worksheet to my SkyDrive here for your reference.

INDEX function
SMALL function
IF function
ROW function
ROWS function
COUNTIF function

Answer 2

Is it possible to use a Vlookup function to sort a table in order. Take for example a basic sports league

Column A is their position (using a rank function to determine this)

Column B is the team name

Column C is their points (these points are automatically updated from another worksheet)

 

eg

 

2        Team A         30

3        Team B         28

3        Team C         28

1        Team D         35

5        Team E          20

 

Therefore the Vlookup table placing them in order: Team D, A, B, C, E

 

Using Pivot Table would be the SIMPLIEST WAY, please see this link on how to create PivotTable and sort the data.

http://www.hsc.wvu.edu/its/LC/Documents/HowTo/MSOffice2003/Pivot_Tables.pdf

Hope it helps,

~jaeson