VLOOKUP Ranking Table help

Hi there

I am using a couple of formulas to draw data from an input data table, then using rank function to rank it, then using a VLOOKUP formula to produce a table of figures in the ranking order.

This all works fine, however the last part of this, the producing of the table is time consuming and I am hoping there is a way to speed things up.

I add the first formula =VLOOKUP(1,lateoct21,6,FALSE) and I have the first name or figure added to the cell, however I have 10 names/figures on each table, I hold bottom right of cell and fill down all 10 cells, this then gives me 10 identical formulas and 10 identical names or figures just copying the first cell. all 10 like this =VLOOKUP(1,lateoct21,6,FALSE). The only way I can get this to work is by clicking on each of the 2nd to 10th cell and manually changing the rank number to 2, 3, 4, 5, 6, 7, 8, 9, 10 until I have all the correct ranks. This works and i get the desired table showing the correct ranks, however I have several tables and this takes so much time.

Is there a way I can fill this formula down with the first number, the ranking number, changing automatically as it goes down so i no longer need to manually change.

All help welcome please