Vlookup in VBA Excel

What is Excel VBA VLOOKUP Function?

In Excel VBA, the VLOOKUP function retrieves data from a table or range based on a specific lookup value. VLOOKUP stands for “Vertical Lookup,” a widely used function to perform vertical searches in a worksheet. It’s commonly used to search for a value in the leftmost column of a table and return a corresponding value from a specified column.

Let us know more about the syntax of the VLOOKUP function in Excel VBA.

Application.WorksheetFunction.VLookup(lookup_value, table_array, col_index, [range_lookup])

Where:

• lookup_value: The value you want to look up in the first column of the table_array.
• table_array: The range of cells that contains the data you want to search through. This should include both the lookup column and the columns from which you want to retrieve data.
• col_index: The relative column index in the table_array from which you want to retrieve data. The leftmost column in the table_array is column 1.
• range_lookup: Optional parameter. If set to True (or omitted), VLOOKUP will perform an approximate match. If set to False, it will perform an exact match.

Consider the following example where we get the type of product “Apple” by performing the VLOOKUP in VBA Excel function in the table given below:

It will result in the output:

How to Use VLookup in Excel VBA?

Now that we know how to implement VLOOKUP in VBA Excel, let us go through some examples.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Examples

Let us look at some examples of how to implement the VLOOKUP in VBA.

Example #1

A company has an employee database recording the distance between each employee’s residence and the office. The company wants to implement a policy where they randomly search for the employee’s name and will call them to work on a Saturday based on their distance in miles. It can be done using VLOOKUP in Excel VBA. Let us see how to do so by referring to the table below:

• Step 1: Start a subroutine to calculate the employee distance.

• Step 2: Initialize variables to accept the input name, the value of the distance and the range/ size of the table.

• Step 3: Set the sheet number in which this subroutine will work.

• Step 4: Get the input from the user.

• Step 5: Set the range/size of the table.

• Step 6: Find the value in the table using the VLOOKUP function and return the value from column 4, the column number of distance and then print the space.

Code:

Sub CalculateEmployeeDistance()
Dim EmpName As String
Dim distance As Long
Dim rng As Range
Set empData = Worksheets(“Example_1”)
EmpName = InputBox(“Enter a name: “)
Set rng = Range(“A2:D9”)
dist = Application.WorksheetFunction.VLookup(EmpName, rng, 4, False)
Debug.Print “Name: ” & EmpName & “, Distance: ” & dist & “km”
End Sub

• Step 7: Run the code implementing the VBA VLOOKUP function.

After this, the output will be printed in the immediate tab.

Example #2

Suppose a shop has a lists catalogue and they have sold the following products in the table below. They need to dynamically update the revenue obtained by selling these items. It can be done using the VLOOKUP function in Excel VBA.

• Step 1: Create a subroutine to calculate the total revenue.

• Step 2: Define two variables rng to define the range of the table, and cell as an iterative integer to be used in a FOR loop.

• Step 3: Define multiple variables in this subroutine, such as a Worksheet variable, the size of the sales table and the size of the product table, an iterative variable, product names, the number of products sold, the price of the product, and the total revenue.

• Step 4: Define the worksheet in which this subroutine will work.

• Step 5: Define the range of the sales table and the product table.

The xlUp is used to imitate the up-arrow key. It is used to check the last non-empty cell in column “A” and returns the size of the rows till the last non-empty row.

• Step 6: Initialize a FOR loop to run through the sales table and define the columns for the product name and quantity sold.

• Step 7: Find the cost of each product by comparing the values of both tables using the VLOOKUP function.

Here, we can see that the VLOOKUP function can also be used to compare the leftmost value between two tables.

• Step 8: Get the total sales revenue by multiplying the quantity and product price. Fill in the values in the third column.

After all the values are parsed through the for loop, it exits automatically and exits the subroutine.

Code:

Sub CalculateTotalRevenue()
Dim salesData As Worksheet
Dim lastRowSales As Long
Dim lastRowProducts As Long
Dim i As Long
Dim productName As String
Dim quantitySold As Double
Dim productPrice As Double
Dim totalRevenue As Double
Set salesData = Worksheets(“Example_2”)
‘define the two tables
lastRowSales = salesData.Cells(salesData.Rows.Count, “A”).End(xlUp).Row
lastRowProducts = salesData.Cells(salesData.Rows.Count, “G”).End(xlUp).Row
For i = 2 To lastRowSales
productName = salesData.Cells(i, 1).Value
quantitySold = salesData.Cells(i, 2).Value
productPrice = Application.WorksheetFunction.VLookup(productName, salesData.Range(“G2:H” & lastRowProducts), 2, False)
totalRevenue = quantitySold * productPrice
salesData.Cells(i, 3).Value = totalRevenue
Next i
End Sub

• Step 9: Run the code for the above table. The output is shown below:

Example #3

To prepare a student report, a college has a list of students studying in various departments whose Grades are displayed. We need to print the average value of all the students to add it to the report card. It can be done using the VBA VLOOKUP function.

Here, we need to print the average grades for each student.

• Step 1: Initialize a sub-procedure to print the average.

• Step 2: Define variables to save the values to be used in the subroutine, namely, the worksheet we’ll be working in, the size of the table, an iterative variable, the name of the students, the grade of the student, the total number of grades and then, the average grade.

• Step 3: Set the worksheet this subroutine will be working in.

• Step 4: Define the size of the table and the list of students.

• Step 5: Initialize a FOR loop where we run through the table and declare the student name variable. This variable value will change upon every successive iteration.

We match the ID number of every student using the VLOOKUP function.
We also use Error handling to keep the code running even if any value does not exist and erase it.

• Step 6: Initialize the total grade and grade count as 0 if there’s no error.

• Step 7: Continuing the If statement, we declare another FOR-loop, create a nested FOR and check whether the grade value corroborates with the student’s ID. If true, we append the grades count by one and add the total grades finding their sum.

• Step 8: After ending the second FOR loop, calculate the average grade by dividing the sum of the grades by the total number of grades, then print it five columns away from the grades column.

• Step 9: Declare an ELSE condition for the VBA IF condition from Step 6 for error handling.

Code:

Sub CalculateAverageGradesInSameSheet()
Dim dataSheet As Worksheet
Dim lastRowStudentData As Long
Dim studentList As Range
Dim studentCell As Range
Dim studentName As String
Dim totalGrades As Double
Dim gradeCount As Long
Dim averageGrade As Double
Set dataSheet = Worksheets(“Example_3”)
lastRowStudentData = dataSheet.Cells(dataSheet.Rows.Count, “A”).End(xlUp).Row
Set studentList = dataSheet.Range(“A2:A” & lastRowStudentData)
For Each studentCell In studentList
studentName = studentCell.Value
On Error Resume Next
studentID = Application.WorksheetFunction.VLookup(studentName, dataSheet.Range(“A2:B” & lastRowStudentData), 2, False)
On Error GoTo 0
If Not IsError(studentID) Then
totalGrades = 0
gradeCount = 0
Dim gradeRow As Range
For Each gradeRow In dataSheet.Range(“A2:A” & lastRowStudentData)
If gradeRow.Value = studentID Then
totalGrades = totalGrades + gradeRow.Offset(0, 2).Value
gradeCount = gradeCount + 1
End If
Next gradeRow
If gradeCount > 0 Then
averageGrade = totalGrades / gradeCount
studentCell.Offset(0, 5).Value = averageGrade
End If
Else
studentCell.Offset(0, 5).Value = “Dept not found”
End If
Next studentCell
End Sub

• Step 10: The output is as shown:

Important Things To Note

• Just like in regular Excel, use VLOOKUP for an exact match unless you specifically need an approximate match. Always ensure that the lookup value matches the lookup range exactly.
• Declare variables to hold references to worksheets, ranges, and other objects you’ll be working on within your VBA code. This enhances code readability and makes it easier to maintain.
• If you’re using VLOOKUP in VBA to analyze data, make sure your data is properly organized. VLOOKUP works best when data is sorted appropriately.
• Since VLOOKUP can return errors if a match isn’t found, use error handling in VBA mechanisms (like On Error Resume Next and On Error GoTo) to gracefully handle such situations and provide feedback to the user.
• Avoid using VLOOKUP without proper error handling in your VBA code. If a lookup doesn’t find a match, it can lead to runtime errors or unexpected behavior.
• Just like in regular Excel formulas, avoid hardcoding lookup values directly into your VBA code. Instead, use variables or constants for flexibility and easier maintenance.
• For very large datasets, VLOOKUP might become slow in VBA as well. Consider more efficient lookup methods like using a dictionary or INDEX/MATCH.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the VLOOKUP in VBA Excel, with its features and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to VLOOKUP in VBA Excel. Here we learn how to use VLOOKUP function in Excel VBA, its syntax, along with examples & points to remember. You can learn more from the following articles –

• VBA Do Until Loop
• VBA DIR Function
• VBA Offset