VLOOKUP In VBA – With Examples

In my earlier post, I had written about VLOOKUP in Excel. It was a massive post of around 2500 words, it explains most of the things about the vertical lookup function in excel. Today’s post is an extension to that post and here we will understand how to apply a VLOOKUP in VBA.

If you haven’t read that post then I would strongly recommend you read that post before going any further. [Read Here]

Assuming that you have basic knowledge of the VLOOKUP function we will move further.

Note: To perform these programs yourself, you may need to enable macros in excel. Read this post to know how to do this.

Syntax of VBA VLOOKUP

You can use VLookUp in macros by following any of the below ways:

Application.VLOOKUP(lookup_value, table_array, column_index, range_lookup)

Or

Application.WorksheetFunction.VLOOKUP(lookup_value, table_array, column_index, range_lookup)

Note: If you are searching for something similar to the VLOOKUP function for Access then probably you should use DLOOKUP.

5 Examples of Using VLOOKUP in VBA

Now let’s move to some practical examples of using VLookUp in VBA codes.

Example 1

Using VLookUp find the monthly salary of “Justin Jones” from the below table. Display the salary using a dialog box.

VLOOKUP in VBA Example 1

Below is the code for this:

Sub FINDSAL() 
Dim E_name As String
E_name = "Justin Jones"
Sal = Application.WorksheetFunction.VLookup(E_name, Sheet1.Range("B3:D13"), 3, False)
MsgBox "Salary is : $ " & Sal
End Sub
Example 1 Message Box

Explanation: In this code, we have used a variable ‘E_name’ to store the employee name whose salary is to be fetched. After this, we have simply supplied the employee name and other required arguments to the VLOOKUP and it returns the salary of the corresponding Employee.

Example 2

Now make the above program a little customizable by accepting the Employee name from the user. If the user enters any Employee name that is not present in the table then the program should be able to convey this clearly to the user.

To accomplish this we can use the below code:

Sub FINDSAL() 
On Error GoTo MyErrorHandler:
Dim E_name As String
E_name = InputBox("Enter the Employee Name :")
If Len(E_name) > 0 Then
Sal = Application.WorksheetFunction.VLookup(E_name, Sheet1.Range("B3:D13"), 3, False)
MsgBox "Salary is : $ " & Sal
Else
MsgBox ("You entered an invalid value")
End If
Exit Sub
MyErrorHandler:
If Err.Number = 1004 Then
MsgBox "Employee Not Present in the table."
End If
End Sub

Explanation: In this code, we are accepting the user input using an InputBox function. If the Employee name entered by the user is found, then VLookUp returns its corresponding salary. However, if the employee name is not present in the table then VLOOKUP throws a “1004 Error”.

And, we have created an error handler to catch such cases for conveying the user that entered employee name doesn’t exist.

Example 3

In this example we will try to write a code that adds the Department field from the Employee Table 1 to our old Employee Table.

VLookup Employee Tables Example 3

As you can see that in both these tables there is only one common column i.e. Employee_ID. So, in this case, we will have to apply the VLookUp based on the Employee ID.

Below is the code to do this:

Sub ADDCLM()
On Error Resume Next
Dim Dept_Row As Long
Dim Dept_Clm As Long
Table1 = Sheet1.Range("A3:A13") ' Employee_ID Column from Employee table
Table2 = Sheet1.Range("H3:I13") ' Range of Employee Table 1
Dept_Row = Sheet1.Range("E3").Row ' Change E3 with the cell from where you need to start populating the Department
Dept_Clm = Sheet1.Range("E3").Column
For Each cl In Table1
Sheet1.Cells(Dept_Row, Dept_Clm) = Application.WorksheetFunction.VLookup(cl, Table2, 2, False)
Dept_Row = Dept_Row + 1
Next cl
MsgBox "Done"
End Sub
VBA VLOOKUP Example 4

Explanation: This code takes each ‘lookup_value’ from the Employee ID field (one at a time), looks up its corresponding Department, and then populates the corresponding department value at the appropriate place.

Please note that in this code we have just pasted the result of the VLOOKUP formula, and not the VLookUp formula itself (Refer Example 5).

Example 4

In this example we will try to write a code that displays all the details of an employee from the Employee table (as shown below) when its Employee ID is entered.

VLookup For Example 4

Below is the code that can accomplish this:

Sub FETCH_EMP_DETAILS()
On Error GoTo MyErrorHandler:
Dim E_id As Long
E_id = InputBox("Enter the Employee ID :")
Det = "Employee ID : " & Application.WorksheetFunction.VLookup(E_id, Sheet1.Range("A3:E13"), 1, False)
Det = Det & vbNewLine & "Employee Name : " & Application.WorksheetFunction.VLookup(E_id, Sheet1.Range("A3:E13"), 2, False)
Det = Det & vbNewLine & "Employee SSN : " & Application.WorksheetFunction.VLookup(E_id, Sheet1.Range("A3:E13"), 3, False)
Det = Det & vbNewLine & "Monthly Salary : " & Application.WorksheetFunction.VLookup(E_id, Sheet1.Range("A3:E13"), 4, False)
Det = Det & vbNewLine & "Department : " & Application.WorksheetFunction.VLookup(E_id, Sheet1.Range("A3:E13"), 5, False)
MsgBox "Employee Details : " & vbNewLine & Det
Exit Sub
MyErrorHandler:
If Err.Number = 1004 Then
MsgBox "Employee Not Present in the table."
ElseIf Err.Number = 13 Then
MsgBox "You have entered an invalid value."
End If
End Sub
Message Box Output Example 4

Explanation: In this example, we have asked the user to enter the Employee Id and then we have used multiple VLookUp Statements and concatenated their outputs to show all the details in a single message box.

Example 5

Redo example 3 but this time paste the whole VLookUp formula instead of pasting only the result.

VLookup Employee Tables Example 3

Below is the code for doing this:

Sub ADDCLM()
On Error Resume Next
Dim Dept_Row As Long
Dim Dept_Clm As Long
ctr = 0
Table1 = Sheet1.Range("A3:A13") ' Employee_ID Column from Employee table
Table2 = Sheet1.Range("H3:I13") ' Range of Employee Table 1
Dept_Row = Sheet1.Range("E3").Row ' Change E3 with the cell from where you need to start populating the Department
Dept_Clm = Sheet1.Range("E3").Column
For Each cl In Table1
Sheet1.Cells(Dept_Row, Dept_Clm).FormulaR1C1 = "=VLOOKUP(RC[-4], R3C8:R13C9, 2, False)"
Dept_Row = Dept_Row + 1
ctr = ctr + 1
Next cl
MsgBox "Done"
End Sub
VLOOKUP in VBA paste formula

Explanation: This code is very similar to the one that we have discussed in Example 3, the only difference between these formulas is that here we are copying the VLookUp formula directly in the cells.

In this code, we have applied the VLOOKUP in R1C1 form. So, the formula =VLOOKUP(RC[-4], R3C8:R13C9, 2, False) means =VLOOKUP(<4 cells to the left of current cell>, <Range of Employee Table 1>, <column to be fetched>, <exact match>).

One thing that is worth noting here is: the square brackets ( [ ] ) in your R1C1 formula indicate that you are specifying a relative range. If you want to specify an absolute range, you need to specify the R1C1 cells without brackets; e.g. R3C8:R13C9.

So, this was all about VBA VLOOKUP .

About Content Studio

Thanks for reading. If you have found this article helpful show your love by sharing it with your friends & colleagues. All the tutorials on the Excel Trick are produced, reviewed, and fact-checked by a team of experts. You can check out our team here.