[Excel] Basic functions

Compare two columns

• see if there are mismatched values (most of them are correct, want to check inconsistency)

Conditional Formatting > Hightlight Cell Rules >  Duplicate Values > Select "Unique"

• see if there are matched values (only want to find a limited amount of similar values)

Conditional Formatting > Hightlight Cell Rules >  Duplicate Values > Select "Duplicate"

Merge two sheets based on the same key/column

=INDEX(input column, MATCH(reference key, keys of input column, 0))

Get the rank by group 

=SUMPRODUCT((A2=$A$2:$A$205)*(B2<$B$2:$B$205))+1
see this webpage