title: Inserting a formula as a cell reference to another formula
body: <p>I am trying to build a formula which will combine OFFSET (because I want to retrieve every other row from one worksheet onto another) and Match()), because I want the function to find the first cell reference using a lookup value. Independently, my index/match works, my Substitute(Address()) works (to retrieve the column#row# instead of the cell value) and my Offset works. However, when I put the substitute(Address()) inside of my Offset function (as the cell reference), the formula stops working.</p>
  
  <p>-Match Function (works, finds the column number)</p>
  
  <p><code>MATCH(T2, 'Sheet1'!1:1, 0)</code></p>
  
  <p>-Substitute (works, switches column alphanumeric for column number)</p>
  
  <p><code>SUBSTITUTE(ADDRESS(1,MATCH(T2, 'Sheet1'!1:1, 0), 4), "1", "")</code></p>
  
  <p>-Offset alone (works, retrieves every other row from sheet 1)</p>
  
  <p><code>OFFSET('Sheet1'!$O$2,(ROW(D3)*2)-6,0)</code></p>
  
  <p>-Final formula (doesn't work)</p>
  
  <p><code>OFFSET('Sheet1'!$SUBSTITUTE(ADDRESS(1,MATCH(T2, 'Sheet1'!1:1, 0), 4), "1", "")$2,(ROW(D3)*2)-6,0)</code></p>
  
  <p>I feel like it must be a problem with inserting the other formulas as a cell reference for OFFSET, but I can't figure out how to fix it.</p>