Get the cell reference of the value found by Excel INDEX function

Question

The Problem

Assume that the active cell contains a formula based on the INDEX function:

=INDEX(myrange, x,y)   

I would like to build a macro that locates the value found value by INDEX and moves the focus there, that is a macro changing the active cell to:

Range("myrange").Cells(x,y)

Doing the job without macros (slow but it works)

Apart from trivially moving the selection to myrange and manually counting x rows y and columns, one can:

1. Copy and paste the formula in another cell as follows:

   =CELL("address", INDEX(myrange, x,y))
   

   (that shows the address of the cell matched by INDEX).

2. Copy the result of the formula above.

3. Hit F5, Ctrl-V, Enter (paste the copied address in the GoTo dialog).

You are now located on the very cell found by the INDEX function.

Now the challenge is to automate these steps (or similar ones) with a macro.

Tentative macros (not working)

Tentative 1

WorksheetFunction.CELL("address", ActiveCell.Formula)

It doesn't work since CELL for some reason is not part of the members of WorksheetFunction.

Tentative 2

This method involves parsing the INDEX-formula.

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

This method actually works, but only for a simple case, where the main INDEX-formula has no nested subformulas.

Note

Obviously in a real case myrange, x and ycan be both simple values, such as =INDEX(A1:D10, 1,1), or values returned from complex expressions. Typically x, y are the results of a MATCH function.

EDIT

It was discovered that some solutions do not work when myrange is located on a sheet different from that hosting =INDEX(myrange ...).

They are common practice in financial reporting, where some sheets have the main statements whose entries are recalled from others via an INDEX+MATCH formula.

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.

Answer 1

The task could be done in one line much simpler than any other method:

Sub GoToIndex()
    Application.Evaluate(ActiveCell.Formula).Select
End Sub

Application.Evaluate(ActiveCell.Formula) returns a range object from which the CELL function gets properties when called from sheets.

EDIT For navigating from another sheet you should first activate the target sheet:

Option Explicit
Sub GoToIndex()
    Dim r As Range
    Set r = Application.Evaluate(ActiveCell.Formula)
    r.Worksheet.Activate
    r.Select
End Sub

Add error handling for a general case:

Option Explicit
Sub GoToIndex()
    Dim r As Range
    On Error Resume Next ' errors off
    Set r = Application.Evaluate(ActiveCell.Formula) ' will work only if the result is a range
    On Error GoTo 0 ' errors on
    If Not (r Is Nothing) Then
        r.Worksheet.Activate
        r.Select
    End If
End Sub

Answer 2

There are several approaches to select the cell that a formula refers to...

Assume the active cell contains: =INDEX(myrange,x,y).

From the Worksheet, you could try any of these:

• Copy the formula from the formula bar and paste into the name box (to the left of the formula bar)
• Define the formula as a name, say A. Then type A into the Goto box or (name box)
• Insert hyperlink > Existing File or Web page > Address: #INDEX(myrange,x,y)
• Adapt the formula to make it a hyperlink: =HYPERLINK("#INDEX(myrange,x,y)")

Or from the VBA editor, either of these should do the trick:

• Application.Goto Activecell.FormulaR1C1
• Range(Activecell.Formula).Select

Additional Note:

If the cell contains a formula that refers to relative references such as =INDEX(A:A,ROW(),1) the last of these would need some tweaking. (Also see: Excel Evaluate formula error). To allow for this you could try:

Range(Evaluate("cell(""address""," & Mid(ActiveCell.Formula, 2) & ")")).Select

This problem doesn't seem to occur with R1C1 references used in Application.Goto or:

ThisWorkbook.FollowHyperlink "#" & mid(ActiveCell.FormulaR1C1,2)

Answer 3

You could use the MATCH() worksheet function or the VBA FIND() method.

EDIT#1

As you correctly pointed out, INDEX will return a value that may appear many times within the range, but INDEX will always return a value from some fixed spot, say

=INDEX(A1:K100,3,7)

will always give the value in cell G3 so the address is "builtin" to the formula

If, however, we have something like:

=INDEX(A1:K100,Z100,Z101)

Then we would require a macro to parse the formula and evaluate the arguments.

Answer 4

Both @lori_m and @V.B. gave brilliant solutions in their own way almost in parallel. Very difficult for me to choose the closing answer, but V.B. even created Dropbox test file, so...

Here I just steal the best from parts from them.

'Move to cell found by Index()
Sub GoToIndex()
    On Error GoTo ErrorHandler
    Application.Goto ActiveCell.FormulaR1C1 ' will work only if the result is a range
    Exit Sub
ErrorHandler:
    MsgBox ("Active cell does not evaluate to a range")
End Sub

I associated this "jump" macro with CTRL-j and it works like a charm.

If you use balance sheet like worksheets (where INDEX-formulas, selecting entries from other sheets, are very common), I really suggest you to try it.