Most of Excel’s sorting features require that you click a button to action the sorting. Using a combination of VLOOKUP and RANK you can get your spreadsheet to automatically sort itself when the results change
In the file you downloaded look for the sheet name ‘VLOOKUP -Auto Sort-1 ‘
Use the function wizard to learn how to use the RANK formulae and what it does.
Using what you’ve learnt about VLOOKUP and RANK, create a section of the spreadsheet which automatically ranks the list of information based on the Total Sales column
- In A4 down, create a formula that ranks the total sales within each row to each other.
- In B13 down and across, use VLOOKUP, to automatically bring through all the relevant information of the person ranked the same as the number in A13 down.
- You will note that there is a problem. This is because 2 of the people have the exact same result and Excel will just pull though the first person
- We therefore have to create a ranking that is absolutely unique.
- In F4 down create a unique identifier. In sports events this may by the number of goals scored or something like that. For now just insert the numbers 1 to 7.
- In order to make the total sales unique you can add the unique numbers in column F. however, if you add them as they are you could accidentally change the results. Therefore you need to turn these into very small numbers that won’t affect the overall result. Take the number in column F, divide it by 10 000 and add it to the Total sales and show the result in column G.
- Now change the rank in A4 down to use the total sales in column G rather then column E.
- Change Adrian’s unit sales from 78 to 1 and see how the table below automatically ranks the list.
To follow along go to 3_5_1
Before you watch the solution, if your answers seem incorrect consider the typical errors made and some useful suggestions:
- This is the typical process of coming up with a solution in Excel. You try a function (RANK). It seems to do the job but you find a problem with it (can’t handle ties). You come up with a way of addressing it.
To follow along go to 3_5_2