Complete the League Table
Now we have calculated each teams league position based on their points, goal difference and goals scored. We now need to lookup that data to pull it into the league table sheet.
Download the EnglishPremierLeague1.4.xlsx spreadsheet to follow along.
Place the Teams in Order in the League Table
The function below should be entered into cell B3 of the League Table sheet to find the team that is top of the league.
The VLOOKUP function looks down column A of the Calculations sheet and uses the MIN function to find the smallest number (position 1). It then returns the contents of column C, which holds the team names, for the record in found in column A.
In cell B4 of the League Table worksheet we shall enter the function below.
This function is similar to the one in B3, however it uses the SMALL function to find the position entered in cell A4 (position 2).
By referring to cell A4, and using a relative reference, we can copy this formula to cells B5:B22 to fill the remaining team names. This finds the 2nd smallest number, then the third smallest and so on.
Lookup League Table Statistics
Now we have the teams in place, we need to look up all the table data such as games played, number of wins and goals scored etc.
The function below should be entered into cell C3.
This VLOOKUP function looks for the team name in cell B3 inside column C of the Calculations sheet, and then returns the value in cell D3 (the 2nd column).
Because of the use of a mixed reference with cell B3 and an absolute reference with cell range C3:T22, this function can be copied across to column O to populate the cells for that team.
The one thing that needs changing is the number 2 in the function above. This represents the number of the column for the data you want returned. So for the number of home wins it should be 3, and the for the last column for points awarded it should be 18.
Each column can then be copied down to row 22 to complete the league table.
Download EnglishPremierLeague1.5.xlsx for a copy of the completed Excel league table.
Popular Excel Tips