Using Index and Match in Excel VBA VLookup function

Question

I have an excel formula that I use often that does a VLookup. It has an embedded Index/Match that gives the last numeric value in the "M" Column.
The Worksheet formula is this:

=VLOOKUP(INDEX($M$10:$M75,MATCH(9.99999999999999E+307,$M$10:$M75)),Data,4)

Cell $M75 being the cell of the row this formula is in. There are numeric, non-numeric and blank cells in Column M but the ID's that I want are always numeric.

So I am trying to write a custom function that would let me simply write =current()

Here is what I have:

Function Current()
  Dim LookupRange As Variant
  Dim indexVar As Variant
  LookupRange = Range("$M$1:M" & ActiveCell.Row)
  indexVar = Application.Index(Range(LookupRange), Application.Match(9.99999999999999E+307, Range(LookupRange)))
  Current = Application.WorksheetFunction.VLookup(indexVar, Worksheets("Info").Range("Data"), 4)
End Function

I have tried using different variable types (String, Long, Variant) but I can't seem to get the function to work.
I don't know if this is enough info but can anyone see if I am missing something?
I only get #VALUE!
Using Excel 2013 on Windows 7

Answer 1

There are some problems with this code that are at odds with your description.

• LookupRange is a variant array. When I test this, it raises a 1004 Method 'Range' of object '_Global' failed error in the assignment to indexVar.
• If I Dim LookupRange As String per your comments, I also get a Type Mismatch error.

Since LookupRange should be a range, I declare it as a range, and use the Set keyword in the assignment statement.

Set lookupRange = Range("$M$10:$M" & ActiveCell.Row)

• Possible typo in your assignment to lookupRange. In your formula, originally you use $M$1, but in the function you use $M$10.

If you start this range at $M$10, and try to evaluate the formula in any cell between row 1-9, you will get an error.

On a related note, if there are no numeric values in the lookupRange, this will return an error, e.g., putting Current() in cell M2 makes a lookup range of M1:M2, wherein I have no numeric data.

I am not sure how you want to handle this, but my answer includes one way to avoid that error. You can modify as needed. Finally:

• Relying on ActiveCell.Row seems like a bad idea, since this formula may recalculate with undesired results.

Instead, make this a required argument for the formula, which you can then call from the worksheet like: =Current(Row()).

Putting it all together, I think this should work, and I provide an example/escape for the match not found error. :

Public Function Current(r As Long)
    Const bigNumber As Double = 9.99999999999999E+307
    Dim wsInfo As Worksheet: Set wsInfo = Worksheets("Info")
    Dim lookupRange As Range
    Dim indexVar As Long
    Dim myVal As Variant

    Set lookupRange = Range("$M$1:$M" & r)
    If Not Application.WorksheetFunction.Sum(lookupRange) = 0 Then
        indexVar = Application.Index(lookupRange, Application.Match(bigNumber, lookupRange))
        myVal = Application.WorksheetFunction.VLookup(indexVar, wsInfo.Range("Data"), 4)
    Else:
        myVal = "No numbers found in: " & lookupRange.Address
    End If
    Current = myVal

End Function

UPDATE FROM COMMENTS

You can add Application.Volatilelink before the variable declarations. This should force re-calculation:

whenever calculation occurs in any cells on the worksheet.

However, this will not force calculation when values on other worksheets (e.g., your Worksheets("Info") is another worksheet) change.

To force recalculation every time you active the sheet containing the =Current(Row()) function, you could put this in the worksheet's code module:

Private Sub Worksheet_Activate()
    Me.Calculate
End Sub

This is probably as close as you can get -- to replicating the native VLOOKUP functionality without actually using a VLOOKUP formula.

Additional notes:

Favoring correctly/strongly-typed variables, I declare indexVar as Long and create a double-precision variable for 9.99999999999999E+307 (it's just easier to work with this way). I also create a worksheet object variable, again, I find them easier to work with.