How to Use VLOOKUP in VBA (4 Ways)

How to Use VLOOKUP

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 we can use this VLOOKUP function in VBA.

Download the Practice Workbook

4 Ways to Use VLOOKUP in VBA

1. Search Data Manually Using VLOOKUP in VBA

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 just find out the employee’s salary using their ID.

Search Data Manually Using VLOOKUP in VBA

Step 1: Select Visual Basic under Developer tab (Shortcut Alt + F11)

Select Visual Basic under Developer tab (Shortcut Alt + F11)

Step 2: Then a window will come. Select Module option under Insert button

Then a window will come. Select Module option under Insert button

Step 3: Now write the following code in the VBA console and press the Run button (Shortcut F5)

Now write the following code in the VBA console and press the Run button (Shortcut F5)

Code:

Sub vlookup_function_1()
 Dim Employee_id As Long
 Dim salary As Long
 Employee_id = 1144
 Set myerange = Range("B4:F11")
 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 information

Now a message pop will come and show the information

2. Search Data with Input Using VLOOKUP in VBA

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.

Search Data with Input Using VLOOKUP in VBA

Step 1: First open the VBA console by following the same Step 1 to Step 2

Step 2: Now enter the following code in the VBA window

VBA code using VLOOKUP function

Code:

Sub vlookup_function_2()
 Set myerange = Range("B4:F11")
 Set ID = Range("D13")
 Set Name = Range("D14")
 Name.Value = Application.WorksheetFunction.VLookup(ID, myerange, 2, False)
End Sub

Step 3: Now Enter any ID in cell D13 and run the code

Now Enter any ID in cell D13 and run the code

Step 4: The Name which is saved against the ID will be shown

The Name which is saved against the ID will be shown

3. Find Information with Input Box Using VLOOKUP in VBA

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.

Find Information with Input Box Using VLOOKUP in VBA

Step 1: First open the VBA window by following the same Step 1 to Step 2

Step 2: Now enter the following code in the VBA console and Run it

VBA code using VLOOKUP function

Code:

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

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

Now again go to the VBA console and enter the full code and run again

Code:

 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 and Department sequentially

Now there will be a pop-up like this picture. Enter ID and Department sequentially

Now there will be a pop-up like this picture. Enter ID and Department sequentially

Step 6: After pressing the Ok button the final output will be shown

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

If you enter the wrong ID or Department, it will show the below message

4. Find Information with Button Using VLOOKUP in VBA

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

First select the Insert option under the Developer tab

Step 2: Then select the Button option from Insert

Then select the Button option from Insert

Step 3: Place the button as per your requirement and give the name of the button

Place the button as per your requirement and give the name of the button

Step 4: Now right-click on the button and select Assign Macro

Now right-click on the button and select Assign Macro

Step 5: Now create a new macro and name it vlookup_function_4

Now create a new macro and name it vlookup_function_4

Step 6: Write the below code in the VBA console and Run the code

Write the below code in the VBA console and Run the code

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("B4:F11")
 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

Now enter any ID and press the button

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.

Conclusion

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.

Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo