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,

Pingback: How to perform Vlookup from Closed Workbook | Excel VBA Training Online

Pingback: Get Data from UserForm into Table | Excel VBA Training Online

Milan OjhaDear Shri Takyar,

I wish to have your help to have a vba like : In an Excel sheet,

to find the fraction of a cell value and replace with another derived value.

Example Case 1:

a) In a cell say A1, the cell value is P1 and HLooking at cell B1(i.e., the next adjacent cell to the right side), the cell value is z, again HLooking at cell C1(i.e., the next adjacent cell to the right side) is z , & so on till it is intercepted by any other letter than z, may be q or any other data.

Note: only z is the criteria to be searched/matched.

The result needed is :

the cell value at A1 i.e., P1 will be replaced as P3 ( i.e., the arithmetic sum of 1 of P1 and 1-each for occurrence of each z,that is 2), adjacent to the horizontal right side cell.

Example Case 2:

likewise in another cell say at B3( of the same worksheet) , the cell value is NP1/4; HLooking at cell C3(i.e., offset each col. horizontally) , the cell value is z; at cell D3, the cell value is again z; at cell E3 the cell value is again z & so on till it is other than z, may be q or any other data.

The result needed is :

Similarly, as in case 1 above, the cell value at B3 will be replaced as NP13/4 ( i.e., the arithmetic sum of numerical suffix parts, 1/4+1+1+1). Kindly, note that the result is to be expressed in pure fraction only and they would appear as suffix to the existing alphabets, here e.g., NP.

Also please note that

i) the cell(s) will be choosen/clicked by the user.

ii) only the fractional part on the right side of the cell

should be ‘Find’ . The fraction will only be ½, 1/3 or ¼ . And maximum number of occurrences of ‘z’ will be 5.

iii) and the replacement should be made on that side i.e., right side of the cell value( like what is done through the ‘Find and Replace ‘ dialog box).

There are huge number of such separate excel sheets. The vba , as such, will be of great help to me.

EVA KOVACShello,

I have got the error message for vlookup2

saying that MyerrorHandler

Sub or function has not defined.

Could you please tell me the reason?

Thanks,

e