• Skip to primary navigation
  • Skip to main content
  • Skip to footer
WallStreetMojo

Wallstreet Mojo

Wallstreet Mojo

MENUMENU
  • Resources
        • Excel

          • Excel Functions
          • Excel Tools
          • Excel Tips
        • Excel
        • Financial Functions Excel

          • NPV in Excel
          • IRR in excel
          • PV in Excel
        • Financial-Functions-Excel
        • Lookup Functions Excel

          • VLOOKUP
          • HLOOKUP
          • Index Function
        • Lookup-Functions-in-Excel
        • Excel Charts

          • Pareto Chart in Excel
          • Gannt Chart in Excel
          • Waterfall Chart in Excel
        • Excel-Charts
        • VBA

          • VBA Left Function
          • VBA Paste Special
          • VBA Worksheet Function
        • VBA
        • Others

          • Resources (A to Z)
          • Financial Modeling
          • Equity Research
          • Private Equity
          • Corporate Finance
          • Financial Statement Analysis
  • Free Courses
  • All Courses
        • Certification Courses

          Excel Course certificate
        • Excel VBA All in One Bundle

          Excel-VBA-Certification-Course
        • Excel Data Analysis Course

          Excel-Data-Analysis-Course
        • VBA Macros Course

          VBA-Training-Course
        • Others

          • Basic Excel Training
          • Advanced Excel Course
          • Tableau Certification Course
          • Excel for Finance Course

          • Excel for Marketers Course
          • Excel for HR Managers
          • Excel for Power Users
          • View All
  • Excel VBA All in One Bundle
  • Login

VLookup in VBA Excel

Home » Excel » Lookup & Reference Functions in Excel » VLookup in VBA Excel

By Sharmila Reddy Leave a Comment

VLookup in Excel Vba

Vlookup is a worksheet function in excel but it can also be used in VBA, the functionality of Vlookup is similar to the functionality in VBA  and in worksheet both, as it is a worksheet function the method to use Vlookup in VBA is through Application.WorksheetFunction method and the arguments remain the same.

VLookup in Excel VBA (Table of Contents)

  • VLookup VBA Excel
  • How to Use VLookup in Excel VBA?

VLOOKUP Function in Excel VBA

The VLOOKUP function in Excel is used to search a value in an array and return its corresponding value from another column. The value to search should be present in the first column. It is also required to mention whether to look for an exact match or an approximate match. The worksheet function VLOOKUP can be used in VBA coding. The function is not built in VBA and thus can only call using the worksheet.

The VLOOKUP function in Excel has the following syntax:

Vlookup formula

In which, lookup_value is the value to look for, table_arrray is the table, col_index_num is the column number of the return value, range_lookup signifies if the match is exact or approximate. range_lookup can be TRUE/FALSE or 0/1.

In VBA code, the VLOOKUP function can be used as:

Application.WorksheetFunction.vlookup(lookup_value, table_array, col_index_num, range_lookup)

How to Use VLookup in Excel VBA?

Below are some examples of VLookup Code in Excel VBA.

You can download this VLookup in Excel VBA Template here – VLookup in Excel VBA Template

VLookup Code in Excel VBA Example #1

Let us see how we can call the worksheet function VLOOKUP in Excel VBA.

Suppose you have a data of students ID, name and the average marks obtained by them.

Vlookup with Vba Example 1

Now, you want to look up the marks obtained by a student, with ID 11004.

To lookup for the value, follow the following steps:

  • Go to the Developer Tab and click on Visual Basic.

Vlookup with Vba Example 1-1

  • Under the VBA window, go to Insert and click on Module.

Vlookup with Vba Example 1-2

  • Now, write the VBA VLOOKUP code. The following VBA VLOOKUP code can be used.

Sub vlookup1()
Dim student_id As Long
Dim marks As Long
student_id = 11004
Set myrange = Range(“B4:D8”)
marks = Application.WorksheetFunction.VLookup(student_id, myrange, 3, False)
End Sub

Firstly, define a student id, which is the value to lookup. Therefore, we define,

student_id = 11004

Next, we define the range in which the value and the return value exist. Since our data is present in the cells B4:D8, we define a range- myrange as:

Set myrange = Range(“B4:D8”)

Finally, we input the VLOOKUP function using the Worksheet function in a variable, marks as:

Popular Course in this category
Cyber Monday Sale
All in One Excel VBA Bundle (35 Courses with Projects) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
4.9 (1,353 ratings)
Course Price

View Course

Related Courses
VBA Macros CourseAdvanced Excel CourseTableau Certification Course

marks = Application.WorksheetFunction.VLookup(student_id, myrange, 3, False)

In order to print the marks in a message box, let us use the following command:

MsgBox “Student with ID:” & student_id & ” obtained ” & marks & ” marks”

Vlookup with Vba Example 1-3

It will return:

Student with ID:11004 obtained 85 marks.

Now, click the run button.

You will notice that a message box will appear in the Excel sheet.

Vlookup with Vba Example 1-4

VLookup Code in Excel VBA Example #2

Suppose you have the data of names of employees and their salary. This data is given the columns B and C. Now, you need to write a VBA VLOOKUP code such that given a name of the employee in a cell, F4, the salary of the employee will be returned in the cell G4.

Vlookup with Vba Example 2

Let us write the VBA VLOOKUP code.

  1. Define the range in which the values are present, i.e., column B and C.

Set myrange = Range (“B:C”)

  1. Define the name of the employee and input the name from the cell F4.

Set name = Range (“F4”)

  1. Define salary as cell G4.

Set salary = Range (“G4”)

  1. Now, call the VLOOKUP function using WorksheetFunction in VBA and input it in salary.Value. This will return the value (output of the Vlookup function) in the cell G4. The following syntax can be used:

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

Vlookup with Vba Example 2-1

  1. Now, run the module. The cell G4 will contain the salary of the employee after you run the VBA VLOOKUP code.

Vlookup with Vba Example 2-2

Suppose you change the value of cell F4 to “David” in the worksheet and re-run the code, it will return the salary of David.

Vlookup with Vba Example 2-3

VLookup Code in Excel VBA Example #3

Suppose you have the data of the employee of your company, having their ID, names, Department, and salary. Using Vlookup in VBA, you want to get the salary details of an employee using his name and department.

Vlookup with Vba Example 3

Since the vlookup function in excel searches the lookup_value in only a single column, which is the first column of the table_array, it is required that you first make a column containing the “Name” and “Department” of each employee.

In VBA, let us insert the values “Name” and “Department” in column B of the worksheet.

To do this, go to the Developer tab and click Visual Basic. Then go to insert and click on Module to start a new module.

Let us now write code, such that column B contains the values of column D (name) and column E.

The syntax is given as:

Vlookup with Vba Example 3-1

Firstly, use a ‘for’ loop from i = 4 since the values are starting from the 4th row in this case. The loop will continue until the end of the last row of column C. So, the variable i can be used as a row number inside the ‘for’ loop.

Then enter the value to assign for Cell (row_number, column B), which can be given as Cells (i, “B”).Value, as Cell (row_number, column D) & “_” & Cell (row_number, column E).

Suppose you want to assign the cell B5 = D5 & “_” & E5, you can simply use the code as:

Cells(5, “B”).Value = Cells(5, “D”).Value & “_” & Cells(5, “E”).Value

Vlookup with Vba Example 3-2

Now, let to look for the lookup value in the array B5:E24, you need to first enter the lookup value. Let us take the value (Name and Department) from the user. To do this,

  1. define three variables, name, department and lookup_val as a string.
  2. Take the name input from the user. Use the code:

name = InputBox (“Enter the name of the employee”)

The content in the input box “Enter the ..” will be displayed in the prompt box when you run the code. The string entered in the prompt will be assigned to the name variable.

  1. Take the department from the user. The can be done similarly as above.

department = InputBox (“Enter the department of the employee”)

  1. Assign the name and department with “_” as a separator to the variable lookup_val using the following syntax:

lookup_val = name & “_” & department

Vlookup with Vba Example 3-3

  1. Write the vlookup syntax to search for the lookup_val in range B5:E24 return it in a variable salary.

Initialize the variable salary:

Dim salary As Long

Use the Vlookup function to find the lookup_val. The table_array can be given as Range(“B:F”) and the salary is present in the 5th column. The following syntax can thus be used:

salary = Application.WorksheetFunction.VLookup(lookup_val, Range (“B:F”), 5, False)

  1. To print the salary in a message box, use the syntax:

MsgBox (The salary of the employee is ” & salary)

Vlookup with Vba Example 3-4

Now, run the code. A prompt box will appear in the worksheet in which you can enter the name. After you enter the name (Say Sashi) and click OK.

Vlookup with Vba Example 3-5

It will open another box in which you can enter the department. After you enter the department, say IT.

Example 3-6

It will print the salary of the employee.

Example 3-7

If the Vlookup can find any employee with the name and department, it will give an error. Suppose you give the name “Vishnu” and department “IT”, it will return Run-time error ‘1004’.

Example 3-8

To address this issue, you can specify in the code, that on this type of error, print “Value not found” instead. To do this,

  1. Before using the vlookup syntax, use the following code-

On Error GoTo Message

Check:

The trailing code (of Check:) will be monitored, and if it receives any error, it will go to the “message” statement

  1. At the end of the code (Before End Sub), specify that if the error number is 1004, then print in the message box “Employee data not present”. This can be done using the syntax:

Message:

If Err.Number = 1004 Then

MsgBox (“Employee data not present”)

End If

Example 3-9

Example 3-10

Module 1:

Sub vlookup3()
For i = 4 To Cells(Rows.Count, “C”).End(xlUp).Row
Cells(i, “B”).Value = Cells(i, “D”).Value & “_” & Cells(i, “E”).Value
Next iDim name As String
Dim department As String
Dim lookup_val As String
Dim salary As Longname = InputBox(“Enter the name of the employee”)
department = InputBox(“Enter the department of the employee”)
lookup_val = name & “_” & departmentOn Error GoTo Message
check:
salary = Application.WorksheetFunction.VLookup(lookup_val, Range (“B:F”), 5, False)
MsgBox (“The salary of the employee is ” & salary)Message:
If Err.Number = 1004 Then
MsgBox (“Employee data not present”)
End IfEnd Sub

Things to Remember About VLookup in Excel VBA

  • The Vlookup function can be called in Excel VBA by using WorksheetFunction.
  • The syntax of vlookup function remains the same in Excel VBA.
  • When VBA vlookup code cannot find the lookup_value, it will give 1004 error.
  • The error in the vlookup function can be managed using a goto statement if it returns an error.

Recommended Articles

This has been a guide to VLookup in VBA. Here we discuss how to write VLookup Code in Excel VBA along with practical examples and downloadable excel template. You may learn more about excel from the following articles –

  • GoTo in VBA
  • Excel VBA Loop
  • VBA MsgBox Function
  • P-Value in Excel | Examples
  • HLOOKUP Examples in Excel
  • VLOOKUP with SUM in Excel
  • VLookup with IF
  • Developer Tab in Excel
  • Top Alternatives to Vlookup
  • Use IFERROR with VLOOKUP
  • Compare VLOOKUP to HLOOKUP
  • Use VLOOKUP with Multiple Criteria
13 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>

Filed Under: Excel, Lookup & Reference Functions in Excel

Reader Interactions
Leave a Reply Cancel reply

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

Footer
COMPANY
About
Reviews
Blog
Contact
Privacy
Terms of Service
FREE COURSES
Free Finance Online Course
Free Accounting Online Course
Free Online Excel Course
Free VBA Course
Free Investment Banking Course
Free Financial Modeling Course
Free Ratio Analysis Course

CERTIFICATION COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Business Valuation Course
Equity Research Course
CFA Level 1 Course
CFA Level 2 Course
Venture Capital Course
Microsoft Excel Course
VBA Macros Course
Accounting Course
Advanced Excel Course
Fixed Income Course
RESOURCES
Investment Banking
Financial Modeling
Equity Research
Private Equity
Excel
Books
Certifications
Accounting
Asset Management
Risk Management

Copyright © 2019. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
WallStreetMojo

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

By continuing above step, you agree to our Terms of Use and Privacy Policy.

* Please provide your correct email id. Login details for this Free course will be emailed to you

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.

* Please provide your correct email id. Login details for this Free course will be emailed to you

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.

* Please provide your correct email id. Login details for this Free course will be emailed to you

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.

* Please provide your correct email id. Login details for this Free course will be emailed to you

WallStreetMojo

Download VLookup in Excel VBA Template

By continuing above step, you agree to our Terms of Use and Privacy Policy.

CYBER WEEK OFFER - All in One Excel VBA Bundle (35 Courses with Projects) View More