It is worth at least being aware that under Office 365 monthly channel some new functions have appeared under the mantle of dynamic arrays.
The function to sort a range in ascending order by the first column is = SORT( Table1 )
Descending it becomes = SORT( Table1, ,-1 )
where the missing parameter specifies the column to sort by. A further parameter allows sorting across columns. More complicated multicolumn sorts are possible using the function SORTBY. To sort the table by column 2 and return only the first entry from column 1 use = @SORTBY(Table1,Table1[Column2],-1)
Then you are left with some variation COUNTIFs for ranking and INDEX for the lookup as shown in Salim Hasan's suggested video from Mike Girvin. Implementing such formulas can get very frustrating when you know that far simpler approaches are available.