title: Indirect formula to find MIN of matching range
body: <p>I have a spreadsheet with two sheets, first sheet contains <code>Name</code> (<code>col:C</code>), and <code>Finish time</code> (<code>col:G</code>), second sheet I have a column also called <code>Name</code>  (<code>col:A</code>) , and a column called <code>Personal Best Time</code> (<code>col:G</code>).</p>
  
  <p>Currently, in <code>Sheet2:Col:G</code>, i'm using a formula to search <code>Sheet1</code> for the lowest <code>Finish time</code> value relating to that particular row's <code>Name</code> column (as below):</p>
  
  <blockquote>
    <p>=ArrayFormula( min( if('Sheet1'!C:C = A2, 'Sheet1'!G:G) ) )</p>
  </blockquote>
  
  <p>But for every row in <code>Sheet2</code>, this formula needs to be copied, with the only difference being the <code>A2</code> reference (which is referring to the <code>Name</code> column of the current row).</p>
  
  <p>So my hope was to create an indirect formula, where the specific range is entered in the header column, and automatically applied to each row.
  So this is what I have so far:</p>
  
  <blockquote>
    <p>=ArrayFormula( IF( ISBLANK(A:A), "", IF( ROW(G:G)=1, "Personal Best Time",   ArrayFormula( min( if( 'Sheet1'!C:C = A2, 'Sheet1'!G:G ) ) )  )))</p>
  </blockquote>
  
  <p>But how do I make it so that <code>A2</code> changes depending on the row number?</p>
  
  <p>I tried <code>A:A</code>, but if I'm right, that would be attempting to compare the whole <code>Name</code> column from <code>Sheet2</code>.</p>