How can we automate the Vlookup process using Excel VBA?

Before we write the VBA code for Vlookup let’s understand how Vlookup works without VBA. Let’s say we have the data as shown in the image below.

Now we wish to find Noah’s salary quickly. Our Vlookup command in any cell in simple language would look like this:

=Vlookup(Value to be looked up, Data containing the value, column in which the looked up value is located, find exact match)

Using Excel’s formula our Vlookup would look like as shown in the image below:

Vlookup assumes that the value to be looked is **always in the first column **of data.

The value to be looked can be in any other column 2, 3, 4, etc. The data range containing all the values in our case for example is, A2:B8. Also, we wish to find exactly how much Noah earns. So in the [lookup_range] value we provide the command ‘FALSE’. This can sometimes be confusing for a beginner but that’s how Excel wants you to enter the parameters for a Vlookup.

Before you start working with the Vlookup VBA code watch the training video:

Based on the above explanation our basic VBA code for Vlookup would look like this:

Sub mylookup1()

Dim emp_name As String

Dim salary As Long

emp_name = “Noah”

Set myrange = Range(“A:B”)

salary = Application.WorksheetFunction.VLookup(emp_name, myrange, 2, False)

MsgBox emp_name & “‘s salary is ” & salary

End Sub

To automate the process we can refine the above code where the user inputs the name of the employee whose salary he wishes to display automatically:

Sub mylookup2()

On Error GoTo MyerrorHandler:

Dim emp_name As String

Dim salary As Long

emp_name = InputBox(“Please enter employee’s name”)

If Len(emp_name) > 0 Then

Set myrange = Range(“A:B”)

salary = Application.WorksheetFunction.VLookup(emp_name, myrange, 2, False)

MsgBox emp_name & “‘s salary is ” & salary

Else

MsgBox “You didn’t enter any name!”

End If

Exit Sub

MyerrorHandler:

If Err.Number = 1004 Then

MsgBox “Employee name not in the data!”

End If

End Sub

You will notice that we also provide an error handling mechanism in the VBA code for Vllookup which takes care of 2 facts:

- The user forgets to enter an employee name and presses enter
- The user enters an employee name that does not exist in our data

The final piece of the Vlookup VBA code provides a way to automate calculations of the salary plus perks that an employee receives:

Sub mylookup3()

Dim lastrow As Long

lastrow = Sheet1.Range(“A” & Rows.Count).End(xlUp).Row

Set myrange = Range(“A:B”)

For i = 2 To lastrow

Cells(i, 3) = Application.WorksheetFunction.VLookup(Cells(i, 1), myrange, 2, False)

Cells(i, 3) = Cells(i, 3) + Cells(i, 3) * 0.3

Next i

End Sub

Further Reading:

VLOOKUP In VBA – With Examples

Power Excel with MrExcel: Master Pivot Tables, Subtotals, Charts, VLOOKUP, IF, Data Analysis in Excel 2010–2013