title: User Defined Function Returning #Value
body: <p>I have come across a situation which required me to average the result of an array of Vlookups. I had no idea how to achieve this with formulas and it seemed like nobody else on StackOverflow had any idea either.</p>
  
  <p>So I decided to write a function to do the job for me. Unfortunately it returns the "#VALUE!" error and I have no idea why! The function works fine when tested with a msgbox. I have annotated my code below:</p>
  
  <pre><code>Option Explicit
  
  Public Function AvgVlookup(Target_Array As String, Lookup_Array As String, Column_Index As Long) As Double
  
  Dim Result As Double
  Dim Total As Double
  Dim Counter As Long
  Dim TargetRange As Range
  Dim LookupRange As Range
  Dim Cell As Range
  
  ' Remove Absolute Indicator
  Target_Array = Replace(Target_Array, "$", "")
  Lookup_Array = Replace(Lookup_Array, "$", "")
  
  ' Convert String to Range
  Set TargetRange = Range(Left(Target_Array, InStr(1, Target_Array, ":") - 1), Mid(Target_Array, InStr(1, Target_Array, ":") + 1))
  Set LookupRange = Range(Left(Lookup_Array, InStr(1, Lookup_Array, ":") - 1), Mid(Lookup_Array, InStr(1, Lookup_Array, ":") + 1))
  
  ' Set Variables to 0
  Counter = 0
  Total = 0
  
  ' For each cell in defined array
  For Each Cell In TargetRange
  
  ' Vlookup the cell and save lookup value to Result variable
      Result = Application.WorksheetFunction.vlookup(Cell, LookupRange, Column_Index, "False")
  
  ' Update variables used to calculate average
      Total = Total + Result
      Counter = Counter + 1
  
  Next Cell
  
  ' Perform calculation
  AvgVlookup = Total / Counter
  
  End Function
  </code></pre>
  
  <hr>
  
  <pre><code>Sub test()
  
  MsgBox AvgVlookup("A5:A8", "G5:H8", 2)
  
  End Sub
  </code></pre>
  
  <p>Any ideas?</p>
  
  <p>Thanks!</p>