I have written a UDF that vlookups values in one workbook based on
department number in another workbook. Everything works fine, except
that for lookup values that do not exist in the lookup table, the vba
formula returns a value. How is this possible? (I have had this
problem before where an excel vlookup returns correct values, but the
vba vlookup returns correct values only some of the time.)


Here is the UDF, followed by the sub that invokes it:

Function VGLookup(storenumber)
'A user defined function to lookup vgs from 'vgs by dept'workbook
'based on dept number on cover tab
Dim rng As Range
Dim dept As String
Set rng = Workbooks("weekly recap temp.xls").Sheets("recap").Range
("q1")
dept = rng
Dim Table As Range
Set Table = Workbooks("vgs by dept.xls").Sheets(dept).Range
("a3:b700")
VGLookup = WorksheetFunction.VLookup(storenumber, Table, 2)
End Function


Sub open_vgs_sheet()
'Assign current path to vg workbook
Dim Path As String
Dim FileName As String
Dim FullName As String

Path = ThisWorkbook.Path
FileName = "VGs by Dept.xls"
FullName = Path & "\" & FileName

'Opens VGs by Dept and inserts VGlookup function into fp units
'tab. Closes "VGs" wkbk without saving changes
Workbooks.Open FileName:=FullName
Windows("weekly recap temp.xls").Activate
Sheets("fp units tyly by store").Activate
Range("m13").Select
ActiveCell.Formula = "=if($d13>0,vglookup($d13),"" "")"
Range("m13", "m700").Select
Selection.FillDown
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Windows("vgs by dept.xls").Activate
ActiveWorkbook.Close SaveChanges = False
Workbooks("weekly recap temp.xls").Sheets("recap").Activate
End Sub