VLookup in VBA Excel

Updated on April 23, 2024
Article byWallstreetmojo Team
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

VLOOKUP is a worksheet function in Excel, but we can also use it in VBA. The functionality of VLOOKUP is similar to the functionality in VBA and worksheets. As it is a worksheet function, the method to use VLOOKUP in VBA is through Application.WorksheetFunction method and the arguments remain the same.

VLOOKUP Function in Excel VBA

The VLOOKUP function in ExcelVLOOKUP Function In ExcelThe VLOOKUP excel function searches for a particular value and returns a corresponding match based on a unique identifier. A unique identifier is uniquely associated with all the records of the database. For instance, employee ID, student roll number, customer contact number, seller email address, etc., are unique identifiers. read more searches a value in an array and returns its corresponding value from another column. The value to search should be present in the first column. It is also required to mention whether to look for an exact or approximate match. We can use the worksheet function VLOOKUP in VBA coding. However, the function is not built-in VBA. Thus, we can only call using the worksheet.

The VLOOKUP function in Excel has the following syntax:

Vlookup formula

In which lookup_value is the value to look for, table_arrray is the table, col_index_num is the column number of the return value, and range_lookup signifies if the match is exact or approximate. The range_lookup can be TRUE/FALSE or 0/1.

In VBA code, one can use the VLOOKUP function:

Application.WorksheetFunction.vlookup(lookup_value, table_array, col_index_num, range_lookup)

How to Use VLookup in Excel VBA?

Below are some examples of the VLOOKUP code in Excel VBA.

You can download this VLookup in Excel VBA Template here – VLookup in Excel VBA Template

–>> If you want to learn Excel VBA professionally, then our VBA Basic Course (16+ hours) is the perfect solution. In our Basic Excel VBA course you learn the skill of automating tasks using Variables, Data Types, Ranges, and Cells in VBA. Master Control Structures, including Conditional Statements and Loops, and discover techniques for manipulating data through sorting, filtering, and formatting. By the end of the course, you will be able to apply your acquired skills to real projects, culminating in an Excel VBA project which will solidify your ability to create efficient, automated solutions.

VLookup Code in Excel VBA Example #1

Let us see how we can call the worksheet function VLOOKUP in Excel VBA.

Suppose you have the data on students’ IDs, names, and average marks.

Vlookup with Vba Example 1

Now, you want to look up the marks obtained by a student with ID 11004.

To lookup for the value, follow the following steps:

  • Go to the “Developer” tab and click on “Visual Basic.”
Vlookup with Vba Example 1-1
  • Under the VBA window, go to “Insert” and click on “Module.”
Vlookup with Vba Example 1-2
  • Now, write the VBA VLOOKUP code. For example, we can use the following VBA VLOOKUP code.

Sub vlookup1()
Dim student_id As Long
Dim marks As Long
student_id = 11004
Set myrange = Range(“B4:D8”)
marks = Application.WorksheetFunction.VLookup(student_id, myrange, 3, False)
End Sub

Firstly, define a student ID, which is the lookup value. Therefore, we define:

student_id = 11004

Next, we define the range in which the value and the return value exist. Since our data is present in the cells B4:D8, we define a range- myrange as:

Set myrange = Range(“B4:D8”)

Finally, we input the VLOOKUP function using the Worksheet function in a variable marks as:

marks = Application.WorksheetFunction.VLookup(student_id, myrange, 3, False)

To print the marks in a message box, let us use the following command:

MsgBox “Student with ID:” & student_id & ” obtained ” & marks & ” marks”

Vlookup with Vba Example 1-3

It will return:

A student with ID:11004 obtained 85 marks.

Now, click the run button.

You will notice that a message box will appear in the Excel sheet.

Vlookup with Vba Example 1-4

VLookup Code in Excel VBA Example #2

Suppose you have the data on the names of employees and their salaries. The data is in columns B and C. Next, you must write a VBA VLOOKUP code, given the employee’s name in cell F4. It will return the employee’s salary in cell G4.

Vlookup with Vba Example 2

Let us write the VBA VLOOKUP code.

  1. Define the range in which the values are present, i.e., columns B and C.

Set myrange = Range (“B:C”)

  1. Define the employee’s name and input the name from cell F4.

Set name = Range (“F4”)

  1. Define salary as cell G4.

Set salary = Range (“G4”)

  1. Now, call the VLOOKUP function using WorksheetFunction in VBA and input it in salary.Value. It will return the value (output of the VLOOKUP function) in cell G4. We can use the following syntax:

salary.Value = Application.WorksheetFunction.VLookup(name, myrange, 2, False)

Vlookup with Vba Example 2-1
  1. Now, run the module. Cell G4 will contain the employee’s salary after you run the VBA VLOOKUP code.
Vlookup with Vba Example 2-2

Suppose you change the value of cell F4 to “David” in the worksheet and re-run the code, which will return David’s salary.

Vlookup with Vba Example 2-3

VLookup Code in Excel VBA Example #3

Suppose you have your company’s employees’ data, ID, names, Department, and salary. Using VLOOKUP in VBA, you want to get the salary details of an employee using his name and department.

Vlookup with Vba Example 3

Since the VLOOKUP function in Excel searches the lookup_value in only a single column, which is the first column of the table_array. It would help if you first made a column containing each employee’s “Name” and “Department.”

In VBA, let us insert the values “Name” and “Department” in column B of the worksheet.

To do this, go to the “Developer” tabDeveloper TabEnabling the developer tab in excel can help the user perform various functions for VBA, Macros and Add-ins like importing and exporting XML, designing forms, etc. This tab is disabled by default on excel; thus, the user needs to enable it first from the options menu.read more and click “Visual Basic.” Then, go to “Insert” and click on “Module” to start a new module.

Let us now write code such that column B contains the values of column D (name) and column E.

The syntax is:

Vlookup with Vba Example 3-1

Firstly, use a ‘for’ loop from i = 4 since the values start from the 4th row in this case. The loop will continue until the end of the last row of column C. So, we can use the variable as a row number inside the ‘for’ loop.

Then, enter the value to assign for Cell (row_number, column B), which we can give as Cells (i, “B”).Value, as Cell (row_number, column D) & “_” & Cell (row_number, column E).

Suppose you want to assign the cell B5 = D5 & “_” & E5. Then, you can simply use the code as:

Cells(5, “B”).Value = Cells(5, “D”).Value & “_” & Cells(5, “E”).Value

Vlookup with Vba Example 3-2

Now, let’s look for the lookup value in the array B5:E24. You need first to enter the lookup value. Let us take the value (Name and Department) from the user. To do this,

  1. Define three variables: name, department, and lookup_val as a string.
  2. Take the name input from the user. Use the code:

name = InputBox (“Enter the name of the employee”)

The content in the input box “Enter the ..” will be displayed in the prompt box when you run the code. The string entered in the prompt will be assigned to the name variable.

  1. Take the department from the user. We can do this similarly as above.

department = InputBox (“Enter the department of the employee”)

  1. Assign the name and department with “_” as a separator to the variable lookup_val using the following syntax:

lookup_val = name & “_” & department

Vlookup with Vba Example 3-3
  1. Write the VLOOKUP syntax to search for the lookup_val in range B5:E24 and return it in a variable salary.

Initialize the variable salary:

Dim salary As Long

Use the VLOOKUP function to find the lookup_val. We can give the table_array as Range(“B:F”), and the salary is in the 5th column. We can thus use the following syntax:

salary = Application.WorksheetFunction.VLookup(lookup_val, Range (“B:F”), 5, False)

  1. To print the salary in a message box, use the syntax:

MsgBox (The salary of the employee is ” & salary)

Vlookup with Vba Example 3-4

Now, run the code. A prompt box will appear in the worksheet where you can enter the name after you enter the name (Say “Sashi”) and click “OK.”

Vlookup with Vba Example 3-5

It will open another box in which you can enter the department. After you enter the department, say IT.

Example 3-6

It will print the salary of the employee.

Example 3-7

If VLOOKUP can’t find any employee with the name and department, it will give an error. For example, suppose you give the name “Vishnu” and the department “IT,” it will return “Run-time error ‘1004’.”

Example 3-8

To address this issue, you can specify that on this type of error, print “Value not found” instead. To do this,

  1. Before using the vlookup syntax, use the following code:

On Error GoTo Message

Check:

The trailing code (of Check:) will be monitored, and if it receives an error, it will go to the “message” statement

  1. At the end of the code (Before End Sub), specify that if the error number is 1004Error Number Is 1004VBA 1004 Error is a runtime error in VBA that is also known as an application-defined or object-defined error. Because Excel has a limited number of columns, we get a 1004 error when our code gives the command to go out of range.read more, then print in the message box, “Employee data not present.” One can do this using the syntax:

Message:

If Err.Number = 1004 Then

MsgBox (“Employee data not present”)

End If

Example 3-9
Example 3-10

Module 1:

Sub vlookup3()
For i = 4 To Cells(Rows.Count, “C”).End(xlUp).Row
Cells(i, “B”).Value = Cells(i, “D”).Value & “_” & Cells(i, “E”).Value
Next iDim name As String
Dim department As String
Dim lookup_val As String
Dim salary As Longname = InputBox(“Enter the name of the employee”)
department = InputBox(“Enter the department of the employee”)
lookup_val = name & “_” & departmentOn Error GoTo Message
check:
salary = Application.WorksheetFunction.VLookup(lookup_val, Range (“B:F”), 5, False)
MsgBox (“The salary of the employee is ” & salary)Message:
If Err.Number = 1004 Then
MsgBox (“Employee data not present”)
End IfEnd Sub

Things to Remember About VLookup in Excel VBA

  • The VLOOKUP function can be called in Excel VBA by using WorksheetFunction.
  • The syntax of the VLOOKUP function remains the same in Excel VBA.
  • When the VBA VLOOKUP code cannot find the lookup_value, it will give a “1004 error.”
  • We can manage the error in the VLOOKUP function using a GoTo statement if it returns an error.

This article has been a guide to VLOOKUP in VBA. Here, we discuss writing VLOOKUP code in Excel VBA, practical examples, and a downloadable Excel template. You may learn more about Excel from the following articles: –

Reader Interactions

Comments

  1. srinisaravanan says

    Thanks a lot for your vba code for vlookup this is what I searched for many times

    Call
    Srini Saravanan
    8925721923
    India
    Tamil Nadu
    Chenna

Leave a Reply

Your email address will not be published. Required fields are marked *