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

Before going to VBA, is your excel function returning correct results? — sam092
– sam092, Commented Sep 30, 2013 at 1:52
Step through your code and determine 1) is the value assigned to LookupRange correct? 2) is the value assigned to indexVar correct? — David Zemens
– David Zemens, Commented Sep 30, 2013 at 2:57
@DavidZemens - My LookupRange shows the correct range if I make it a string. And if I make the indexVar a string and use a macro to insert a vLookup formula into a cell on my sheet it gives the correct ID number. But Strings don't work when I try doing the Vlookup in VBA. — Brad
– Brad, Commented Sep 30, 2013 at 15:47

Ram · Accepted Answer · 2015-07-14 18:53:35Z

3

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.

enter image description here

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.

edited Jul 14, 2015 at 18:53

Ram

3,09110 gold badges43 silver badges57 bronze badges

answered Sep 30, 2013 at 16:39

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Brad Over a year ago

That works and gives me the correct information. The interesting thing is it doesn't recalculate if the number it would have found changes. Is this because of the index/match?

Brad Over a year ago

It will update if I enter the cell but not by itself as other Vlookup functions do. Is this because of the index/match only executes once when the function is entered?

David Zemens Over a year ago

This isn't a Vlookup function :) it's simply using the Vlookup method to search for a matching offset value. I'll add some additional comments to my answer to clarify/suggest what you might do.

David Zemens Over a year ago

If this solves your problem @Brad please mark the answer as "Accepted". Cheers!

Brad Over a year ago

Thank you for all your help. It's weird because I have a function that uses the same vlookup method except instead of calculating 'indexVar' with index/match I give it a cell that contains a index/match formula. And that recalculates any time there is a change.

|

Collectives™ on Stack Overflow

Using Index and Match in Excel VBA VLookup function

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related