MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

VBA Vlookup returning wrong values

  Asked By: Gwendolyn    Date: Feb 18    Category: MS Office    Views: 2098

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
dept = rng
Dim Table As Range
Set Table = Workbooks("vgs by dept.xls").Sheets(dept).Range
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
ActiveCell.Formula = "=if($d13>0,vglookup($d13),"" "")"
Range("m13", "m700").Select
Selection.PasteSpecial Paste:=xlPasteValues
Windows("vgs by dept.xls").Activate
ActiveWorkbook.Close SaveChanges = False
Workbooks("weekly recap temp.xls").Sheets("recap").Activate
End Sub



1 Answer Found

Answer #1    Answered By: Camille Garrett     Answered On: Feb 18

VLOOKUP accepts one more argument which you are not providing. This argument,
called Range_lookup, determines if VLOOKUP returns  an exact match or an
approximate match. Here is the Excel Help info about Range_lookup:

Range_lookup is a logical value that specifies whether you want VLOOKUP to
find an exact match or an approximate match. If TRUE or omitted, an approximate
match is returned. In other words, if an exact match is not found, the next
largest value that is less than lookup_value is returned. If FALSE, VLOOKUP will
find an exact match. If one is not found, the error value #N/A is returned.

Add a FALSE Range_lookup argument to your VLOOKUP calls, and I think your
problem will be solved. Also, by specifying that you want an exact match, the
range you are searching need not be sorted first.

Didn't find what you were looking for? Find more on VBA Vlookup returning wrong values Or get search suggestion and latest updates.