title: Get the cell reference of the value found by Excel INDEX function
body: <h2>The Problem</h2>
  
  <p>Assume that the <em>active cell</em> contains a formula based on the <code>INDEX</code> function:</p>
  
  <pre><code>=INDEX(myrange, x,y)   
  </code></pre>
  
  <p>I would like to build a macro that locates the value found value by <code>INDEX</code> and moves the focus there, that is a <em>macro changing the active cell</em> to:  </p>
  
  <pre><code>Range("myrange").Cells(x,y)
  </code></pre>
  
  <h2>Doing the job without macros (slow but it works)</h2>
  
  <p>Apart from  trivially moving the selection to <code>myrange</code> and manually counting <code>x</code> rows <code>y</code> and columns, one can:    </p>
  
  <ol>
  <li><p>Copy and paste the formula in another cell as follows:</p>
  
  <pre><code>=CELL("address", INDEX(myrange, x,y))
  </code></pre>
  
  <p>(that shows the address of the cell matched by INDEX).  </p></li>
  <li><p>Copy the result of the formula above.</p></li>
  <li><p>Hit <code>F5</code>, <code>Ctrl-V</code>, <code>Enter</code> (paste the copied address in the <code>GoTo</code> dialog).</p></li>
  </ol>
  
  <p>You are now located on the very cell found by the <code>INDEX</code> function.</p>
  
  <p>Now the challenge is to automate these steps (or similar ones) with a macro. </p>
  
  <h2>Tentative macros (not working)</h2>
  
  <p><em>Tentative 1</em></p>
  
  <pre><code>WorksheetFunction.CELL("address", ActiveCell.Formula)
  </code></pre>
  
  <p>It doesn't work since <code>CELL</code> for some reason is not part of the members of <code>WorksheetFunction</code>. </p>
  
  <p><em>Tentative 2</em></p>
  
  <p>This method involves parsing the <code>INDEX</code>-formula. </p>
  
  <pre><code>Sub GoToIndex()
  Dim form As String, rng As String, row As String, col As String
  
      form = ActiveCell.Formula
      form = Split(form, "(")(1)
      rng = Split(form, ",")(0)
      row = Split(form, ",")(1)
      col = Split(Split(form, ",")(2), ")")(0)
      Range(rng).Cells(row, CInt(col)).Select
  
  End Sub
  </code></pre>
  
  <p>This method actually works, but only for a simple case, where the main <code>INDEX</code>-formula has no nested subformulas. </p>
  
  <h2>Note</h2>
  
  <p>Obviously in a real case <code>myrange</code>, <code>x</code> and <code>y</code>can be both simple values, such as  <code>=INDEX(A1:D10, 1,1)</code>, or  values returned from complex expressions. Typically <code>x</code>, <code>y</code> are the results of a <code>MATCH</code> function. </p>
  
  <h2>EDIT</h2>
  
  <p>It was discovered that some solutions do not work when <code>myrange</code> is located on a sheet different from that hosting <code>=INDEX(myrange ...)</code>.</p>
  
  <p>They are common practice in financial reporting, where some sheets have  the main statements whose entries are recalled from  others via an <code>INDEX+MATCH</code> formula. </p>
  
  <p>Unfortunately it is just when  the found value is located on a "far" report out of sight that you need more the jump-to-the-cell function.</p>