|There are multiple ways to reference a cell to the left of a value.|
One method is to use OFFSET and MATCH:
This formula will MATCH the value from Sheet2!B2 in the range SHEET1!$B$2:$B$10 and return position of the player number in that list. It will then use that number as the rows argument (after we subtract 1 from it) for the OFFSET function. -1 as the cols argument will reference the column to the left.
Another method is SUMIF:
=SUMIF(Sheet1!$B$2:$B$10, Sheet2!B2, Sheet1!$A$2:$A$10)
Since there is only one occurrence of each player number in the range, the SUMIF will only return a single rank value for each player.
You could subtract 2 SUMIF's to get the change in Rank directly.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.