title: Return max index value in vlookup
body: <p>I'm trying to pair a vlookup with a max function.  For some reason it only returns #ref every time I try to use it though.</p>
  
  <p>My sheet looks like this:</p>
  
  <pre><code>      A  -  B  -  C  -  D  -  E  -  F  -  G
  1...
  5 - Prod5  id1  $100  $125  $155  $110  $150
  6...
  </code></pre>
  
  <p>A:G is named buyAverages
  C:G is named buyAveragesPrices</p>
  
  <p>What I want to do is have a vlookup go and find a value in Col A and then return the highest value in that Col.  So example:</p>
  
  <pre><code>      A  -  B
  1 - Prod5 *return highest price for Prod5
  </code></pre>
  
  <p>What I wrote in B1, which failed:</p>
  
  <pre><code>VLOOKUP(A1,buyAverages,MAX(buyAveragesPrices))
  </code></pre>
  
  <p>So how do I achieve this lookup?  Everything I have found is how to use MAX for the lookup value, but nothing to use max on the returned index.</p>