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

w3soulspiritDear sir,

I have working as loan recovery agent and facing difficulties in followup. So come up with the excel vlookup but still it can show exits data can’t update in exist one direct because its save lot of time can you please help me out problem.

ChelHi there! I’m new at VBA and I’d want to ask help for this:

I have a combobox in a userform which has 2 options (sick leave and vacation leave). If the user indicates vacation leave in the said combobox, the code should compute the number of days between the dates entered in textbox1 (start date) and textbox2 (end date) and should be entered in column E (of the same row with that of the dates entered) of Sheet1.

Otherwise, if it is sick leave ten the number of days between the dates entered in textbox1 (start date) and textbox2 (end date) and should be entered in column F of the same row and same Sheet.

The start date and end date should be entered at columns C and D, respectively.

Nadineon this line of code using Excel 2013 —

Sub mylookup3()

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

I am getting runtime error 1004

Unable to get the Vlookup property of the WorkSheetFunction class.

I have copied/pasted from the example on website and still get the error.

Pingback: How to use VLookup to get multiple values | Excel VBA Training Videos

ShazDear Sir,

Just wanting your kind help to convert the formula into worksheet.function.

Sub getdata()

Dim lr1 As Long, lr2 As Long, lval As String

Dim SIrange As Range, MECLrange As Range

Dim countx As Long

Dim mecldsr As Worksheet

Set mecldsr = Sheets(“MTD-AMT”)

Dim sellinwb1 As Worksheet

Workbooks(“Sellin.xlsx”).Activate

Set sellinwb1 = Sheets(“Amt”)

‘lr1 = sellinwb1.Cells(Rows.Count, 1).End(xlUp).Row

Set SIrange = Range(“A:AA”)

Workbooks(“MECL DSR.xlsx”).Activate

lr2 = mecldsr.Cells(Rows.Count, 1).End(xlUp).Row

Set MECLrange = Range(Cells(1, 1), Cells(1, lr2))

For countx = 9 To lr2

lval = Cells(countx, 2)

If Cells(countx, 1).Value = “Y” Then

Range(“W” & countx).Formula = “=IFERROR(INDEX([Sellin.xlsx]Amt!R3C1:R1048576C27,MATCH(RC2,[Sellin.xlsx]Amt!C1,0),MATCH(R2C[-5],[Sellin.xlsx]Amt!R3C1:R3C27,0)),0)”

Range(“W” & countx) = Range(“W” & countx)

Range(“AG” & countx).Formula = “=IFERROR(INDEX([Sellin.xlsx]Amt!R3C1:R1048576C27,MATCH(RC2,[Sellin.xlsx]Amt!C1,0),MATCH(R2C[-5],[Sellin.xlsx]Amt!R3C1:R3C27,0)),0)”

Range(“AG” & countx) = Range(“AG” & countx)

End If

Next

End Sub

Thanks,