In your comparison sheet you would have (pre-filled) the ID and Name of
the Affiliate, with other columns as I outlined earlier, so:

A --ID, B --Name, C --Previous Sales, D -- Latest Sales, E --
Movement, F -- Rank

The data will begin on row 2 and let's say it goes down to row 20. In
C2 you would have a formula along the lines of:

= VLOOKUP(A2,range_in_previous,2,0) and in D2:

= VLOOKUP(A2,range_in_current,2,0)

where range_in_previous is the range covered in the sheet for the
previous month and will look something like "Previous!$A$2:$B$20" if
the name of the sheet is "Previous". Similarly, range_in_current would
look like "Current!$A$2:$B$20", where Current is the name of this
sheet. The formula in C will look in the previous sheet to get the
value that corresponds to a match with the ID stored in column A,
whereas the formula in D will look in the current month's sheet. None
of the 3 sheets need to be in the same sequence for this to work, as
you are looking for an exact match by setting the final parameter in
the formulae to "0".

You say you understand how to get the "movement" and rank values, so I
hope this helps.

Pete