Compare two values in two rows and return the higher value for two categories.

Hi Experts,

Case:
There are two levels of salesmen in a branch - Tier 1 or Tier 2. In a branch, Tier 2 salesmen cannot have higher pay than a Tier 1 salesmen. Salesmen dummy codes are in the example sheet.

Solution needed: I looking for a formula which compares the values for tier 1 & 2 and results if a value for tier 2 is higher. E.g. T235 cannot have higher value than T125, it doesn't matter if value for T235 is higher than other Tier codes (Column G in the below screenshot).


Example.JPG