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 vertical look up 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 to read that post before going any further. [Read Here]

Assuming that you have basic knowledge of 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:

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

Or

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

Note: If you are searching for something similar to 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 customisable by accepting the Employee name from user. If 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 appropriate place.

Please note that in this code we have just pasted the result of 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 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 the 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 with 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 to note 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 Ankit Kaul

Ankit is the founder of Excel Trick. He is tech Geek who loves to sit in front of his square headed girlfriend (his PC) all day long. :D. Ankit has a strong passion for learning Microsoft Excel. His only aim is to turn you guys into 'Excel Geeks'.
You can find Ankit on FaceBook or Google Plus

Comments

  1. Rahim Zulfiqar Ali says:

    Simply Awesome :) Thanks

  2. Rahim Zulfiqar Ali says:

    I have created a webpage https://sites.google.com/site/vlookupweek2012/
    Its related to VLOOKUP with more examples. You can share on your Blog. You can also re-write if you want. Thank you

  3. Thanks

Speak Your Mind

*

Privacy Policy | Disclaimer | About