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.

Below is the code for this:

view plaincopy to clipboardprint?

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

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:

view plaincopy to clipboardprint?

1. Sub FINDSAL()  
2. On Error GoTo MyErrorHandler:  
3. Dim E_name As String  
4. E_name = InputBox("Enter the Employee Name :")  
5. If Len(E_name) > 0 Then  
6.   Sal = Application.WorksheetFunction.VLookup(E_name, Sheet1.Range("B3:D13"), 3, False)  
7.   MsgBox "Salary is : $ " & Sal  
8. Else  
9.   MsgBox ("You entered an invalid value")  
10. End If  
11. Exit Sub  
12. MyErrorHandler:  
13. If Err.Number = 1004 Then  
14.   MsgBox "Employee Not Present in the table."  
15. End If  
16. 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.

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:

view plaincopy to clipboardprint?

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

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.

Below is the code that can accomplish this:

view plaincopy to clipboardprint?

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

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.

Below is the code for doing this:

view plaincopy to clipboardprint?

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

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.