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 property, 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 calling 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.

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

VBA Vlookup Example

The following simple VBA function receives an employee name and a range of cells that contain rates of pay, as shown in the example spreadsheet on the right.

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

In the example below, the supplied employee name is "Benson, Paul" and the supplied range is A1:B8. 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 )