In terms of searching or seeing some specific values or elements, MS Excel provides various functions. VLOOKUP is one of them. It helps us to search for desired data from any dataset. It allows us to mention whether to look for an exact match or an approximate match. Along with using this function in the Excel formula, we can use this in VBA code also. In this article, we will see how to use VLOOKUP function in Excel VBA.
Download the Practice Workbook
4 Ways to Use VLOOKUP in VBA in Excel
Let’s have an employee information dataset with their ID, Name, Department, Joining Date, and Salary.
Now our task is to find out employee information using their ID manually. For this section, we will find the employee’s salary using their ID.
In this section, you will see 4 possible ways to perform this task with Excel VBA.
1. Searching Data Manually Using Message Box
Let’s check out the steps for searching data manually using message box in VBA.
➤ Step 1: Select Visual Basic under the Developer tab (or just press ALT + F11).
➤ Step 2: Then a window will come. Select the Module option under the Insert button.
➤ Step 3: Now write the following code in the VBA console and press the Run button (Shortcut F5).
Sub vlookup_function_1() Dim Employee_id As Long Dim salary As Long Employee_id = 1144 Set myerange = Range("B5:F12") salary = Application.WorksheetFunction.VLookup(Employee_id, myerange, 5, False) MsgBox "Employee ID:" & Employee_id & " Salary " & "$" & salary End Sub
➤ Step 4: Now a message pop will come and show the searched information.
Read More: VLOOKUP to Search Text in Excel (4 Easy Ways)
2. Looking for Data with Particular Input
Now let’s see how we can search or extract data from tables or ranges with input. Like the picture, we will find out the name of the entered id from the employee information table.
➤ Step 1: First open the VBA console by following the same Step 1 to Step 2 of Method 1.
➤ Step 2: Now enter the following code in the VBA window.
Sub vlookup_function_2() Set myerange = Range("B5:F12") Set ID = Range("D14") Set Name = Range("D15") Name.Value = Application.WorksheetFunction.VLookup(ID, myerange, 2, False) End Sub
➤ Step 3: Now enter any ID in cell D14 and Run the code.
➤ Step 4: The Name which is saved against the ID will be shown.
Read More: 10 Best Practices with VLOOKUP in Excel
- VLOOKUP Not Working (8 Reasons & Solutions)
- INDEX MATCH vs VLOOKUP Function (9 Examples)
- Use VLOOKUP with Multiple Criteria in Excel (6 Methods + Alternatives)
- Excel VLOOKUP to Find Last Value in Column (with Alternatives)
- Excel VLOOKUP to Return Multiple Values Vertically
3. Finding Information with Input Box
Let’s see how we can search data using VBA’s input box. For searching, we need to use the VLOOKUP function in the VBA code. Again, the dataset will be the same, but the searching approach will be different. Here our task is to find out the employee’s salary by entering the ID and Department.
➤ Step 1: First open the VBA window by following the same Step 1 to Step 2 of Method 1.
➤ Step 2: Now enter the following code in the VBA console and Run it.
Sub vlookup_function_3() For i = 4 To Cells(Rows.Count, "B").End(xlUp).Row Cells(i, "A").Value = Cells(i, "B").Value & "_" & Cells(i, "D").Value Next i End Sub
➤ Step 3: This will print a combined string with ID and Department in the first column.
➤ Step 4: Now again go to the VBA console and enter the full code and Run again.
Sub vlookup_function_3() For i = 4 To Cells(Rows.Count, "B").End(xlUp).Row Cells(i, "A").Value = Cells(i, "B").Value & "_" & Cells(i, "D").Value Next i Dim ID As Long Dim department As String Dim lookup_val As String Dim salary As Long ID = InputBox("Enter the ID of the employee") department = InputBox("Enter the department of the employee") lookup_val = ID & "_" & department On Error GoTo Message check: salary = Application.WorksheetFunction.VLookup(lookup_val, Range("A:F"), 6, False) MsgBox ("The salary of the employee is $" & salary) Message: If Err.Number = 1004 Then MsgBox ("Employee data not present") End If End Sub
💡 Code Explanation
- Firstly, Cells(i, “A”).Value = Cells(i, “B”).Value & “_” & Cells(i, “D”).Value using this code we are storing the ID and Department concatenated values into A column.
- lookup_val = ID & “_” & department this is defining that the lookup value will be ID and department.
- salary = Application.WorksheetFunction.VLookup(lookup_val, Range(“A: F”), 6, False) here we are storing the salary of the matched employee into a variable named salary
- If Err. Number = 1004 Then this is condition checking. We are checking if the error number is 1004 or not. In Excel VBA 1004 code means the searched value is not found, deleted, or removed.
➤ Step 5: Now there will be a pop-up like this picture. Enter ID in the first pop up.
Then enter Department in the next pop up.
➤ Step 6: After pressing the OK button the final output will be shown.
➤ Step 7: If you enter the wrong ID or Department, it will show the below message.
Read More: How to Use VLOOKUP to Find Duplicates in Two Columns
4. Insert Button to Find Data
Now we will see how we can find out information with the help of a button instead of running the code manually. Again, the dataset will be the same as above.
➤ Step 1: First select the Insert option under the Developer tab and select the Button option from Insert.
➤ Step 2: Place the button as per your requirement and give the name of the button (i.e. Find Salary).
➤ Step 3: Now right-click on the button and select Assign Macro.
➤ Step 4: Now create a new macro and name it vlookup_function_4.
➤ Step 5: Write the below code in the VBA console and Run the code
Sub vlookup_function_4() Dim rng As Range, FinalResult As Variant, Table_Range As Range, LookupValue As Range Set rng = Sheets("Sheet4").Range("D15") Set Table_Range = Sheets("Sheet4").Range("B5:F12") Set LookupValue = Sheets("Sheet4").Range("D14") FinalResult = Application.WorksheetFunction.VLookup(LookupValue, Table_Range, 5, False) rng = FinalResult End Sub
➤ Step 6: Now enter any ID and press the Button.
Read More: How to Find Duplicate Values Using VLOOKUP in Excel
Things to Remember
|Common Errors||When They Show|
|1004 error||When the VBA VLOOKUP code cannot find the lookup_value, it will give a 1004 error.|
|VLOOKUP Function Not Found in VBA||The VLOOKUP function can be called in Excel VBA by using WorksheetFunction.|
|Error Handling||The error in the VLOOKUP function can be controlled using a goto statement if it returns an error.|
These are some ways to use the VLOOKUP function in VBA in Excel. I have shown all the methods with their respective examples but there can be many other iterations. I have also discussed the fundamentals of the used functions. If you have any other method of achieving this, then please feel free to share it with us.
- VLOOKUP to Return Multiple Columns in Excel (4 Examples)
- How to Use VLOOKUP for Rows in Excel (With Alternatives)
- VLOOKUP with Multiple Criteria in Excel (6 Examples)
- How to Use the VLOOKUP Ascending Order in Excel (3 Ways)
- VLOOKUP with Two Lookup Values in Excel (2 Approaches)
- How to Apply Double VLOOKUP in Excel (4 Quick Ways)