title: Multiple occurrences of MAX values in excel - select MAX value that has highest value in adjacent cell
body: <p>I have a spreadsheet with weight lifting data that has:</p>
  
  <ol>
  <li>number of reps and</li>
  <li>total weight lifted (for those reps)</li>
  </ol>
  
  <p>I want to reserve a section at the bottom of the spreedsheet to record personal bests. I want to do two things in that respect:</p>
  
  <ol>
  <li>find the heaviest weight that has been lifted</li>
  <li>for that identified heaviest weight, I want to know how many reps the weight was lifted</li>
  </ol>
  
  <p>I have managed to do this by using MAX and OFFSET functions but here is the problem:</p>
  
  <ul>
  <li>The MAX value found for the heaviest weight occurs more than once and excel just finds the first instance of this MAX value and reports back the reps that it was lifted. In my example this is 4 reps. </li>
  </ul>
  
  <p>What I want excel to do is find the heaviest weight lifted for the most reps (i.e., if the second occurrence of the heaviest weight lifted was lifted for more reps than the first instance, I want that value reported). In my example this is 5 reps. </p>
  
  <pre><code>|Rep  Weight |Rep  Weight |Rep  Weight |Rep  Weight |Rep  Weight |
  | 6    120   | 8    120   | 10   120   | 4     140  | 5     140  |
  
  
   Personal best
  |Rep   Weight |
  | 5      140  |
  </code></pre>