Vlookup Excel VBA

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.

Vlookup Using VBA
Vlookup Using VBA

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 Formula
Vlookup Formula

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:

  1. The user forgets to enter an employee name and presses enter
  2. 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

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

9 thoughts on “Vlookup Excel VBA”

  1. Dear 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.

  2. Hi 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.

  3. on 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.

  4. Dear 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,

  5. Dear 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.

  6. hello,
    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

Leave a Reply

Your email address will not be published. Required fields are marked *