title: Using Index and Match in Excel VBA VLookup function
body: <p>I have an excel formula that I use often that does a <code>VLookup</code>. It has an embedded <code>Index/Match</code> that gives the last numeric value in the "M" Column. <br>
  The Worksheet formula is this:</p>
  
  <pre><code>=VLOOKUP(INDEX($M$10:$M75,MATCH(9.99999999999999E+307,$M$10:$M75)),Data,4)
  </code></pre>
  
  <p>Cell <code>$M75</code> 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.</p>
  
  <p>So I am trying to write a custom function that would let me simply write <code>=current()</code></p>
  
  <p>Here is what I have:</p>
  
  <pre><code>Function Current()
    Dim LookupRange As Variant
    Dim indexVar As Variant
    LookupRange = Range("$M$1:M" &amp; 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
  </code></pre>
  
  <p>I have tried using different variable types (String, Long, Variant) but I can't seem to get the function to work. <br>
  I don't know if this is enough info but can anyone see if I am missing something? <br>
  I only get <code>#VALUE!</code><br>
  Using Excel 2013 on Windows 7</p>