VBA Vlookup - Use the Excel Vlookup Function in VBA

Although there is no built-in VBA Vlookup function, you can still call the Excel Vlookup function from within your VBA code.

In fact, you can call any of the built-in Excel functions from within your VBA code, through the VBA Application object, which represents the Microsoft Excel application.

One of the object members of the Application is the WorksheetFunction object, which acts as a container for all of the built-in Excel Worksheet functions.

Therefore, any of the built-in Excel functions can be accessed in VBA, using the general command:

Result = Application.WorksheetFunction.Function Name ( function arguments )

The syntax for the VBA Vlookup function call is therefore:

Result = Application.WorksheetFunction.Vlookup ( lookup_value, table_array, col_index_num, [range_lookup] )

This is illustrated in the VBA Vlookup example below.

VBA Vlookup Example

1Employee NameHourly Rate
2Atkins, James$35.50
3Bamber, Carol$39.00
4Benson, Paul$32.50
5Cooper, David$29.75
6Daniels, Tom$44.00
7Taylor, Lisa$27.90
8Wells, Gail$35.00

The following simple VBA code uses the example spreadsheet aboveon the right, which lists seven employee names alongside their individual rates of pay.

The VBA code calls the Worksheet Vlookup function, and uses this to look up the rate of pay for a specific employee name.

In the example, the specified employee name is "Benson, Paul". Therefore, the VBA Vlookup function returns the value $32.50.

' Code to look up the rate of pay for a supplied employee name and a supplied range of cells
' containing hourly pay rates
Dim Employee_Name As String
Dim Lookup_Range As Range
Dim Pay_Rate As Single
Employee_Name = "Benson, Paul"
Set Lookup_Range = Range("A1:B8")
' Call the Vlookup function to look up the rate of pay for the supplied employee name
' This will look up the value in the second column of the range A1:B8 in the current worksheet.
Pay_Rate = Application.WorksheetFunction.VLookup( Employee_Name, Lookup_Range, 2, False )