EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login

VBA VLOOKUP Function

By Jeevan A YJeevan A Y

Home » VBA » Blog » VBA Lookup & Reference Functions » VBA VLOOKUP Function

VBA VLOOKUP Function

VBA VLOOKUP Function (Table of Contents)

  • Where to Write Macros?
  • How to Use VBA VLOOKUP in Excel?

What is VBA VLOOKUP Function?

Vlookup function of excel can be used in the VBA as well with the same syntax that we used in Excel.  Open a module in VBA and define a variable for Lookup value and then declare the cell where we will be applying Vlookup using VBA. Now as per syntax of Vlookup in VBA select the lookup value, lookup range or table, Column index number and range lookup is TRUE or FALSE. Now, if we run the code, then we will be able to see the lookup value in a declared cell in Excel.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Formula of VLOOKUP Function

The Formula includes 4 mandatory arguments.

VLOOKUP Syntax

  • Lookup Value: This is the base values you are searching. Based on this we look for a value in the table.
  • Table Value: This is the table which holds all the values. From this table using lookup value, we will fetch the data.
  • Column Index Number: This is the column number from the table we are looking for. In a table, there are many columns, out of many columns we only require the column which we are looking for the data.
  • Range Lookup: Here we need to specify what kind of result we want. If we want the exact match we need to mention as FALSE or 0, if we want the approximate match we can mention as TRUE or 1.

Where to Write Macros?

VBA is accommodated under Developer tab in excel. If you are not able to find the developer tab follow the below steps to unhide this.

Step 1: Click on File.

VBA VLOOKUP Step 1

Step 2: Under File, click on Options.

VBA VLOOKUP Step 2

Step 3: Select Customize Ribbon.

VBA VLOOKUP Step 3

Step 4: On the right-hand side make sure the checkbox of the Developer tab is ticked.

VBA VLOOKUP Step 4

Now you must see the Developer tab in the Ribbon.

VBA VLOOKUP Step 5

How to Use VBA VLOOKUP Function in Excel?

VBA VLOOKUP Function is very simple to use. Let us now see how to use VBA VLOOKUP function in Excel with the help of some examples.

You can download this VBA VLOOKUP Excel Template here – VBA VLOOKUP Excel Template

Example #1

I have a sales table for different products. It has product name and sales made against that product.

VBA VLOOKUP Example 1-1

In cell E2, I want to know the sales value of TV using VBA VLOOKUP function.

Step 1: Go to the Developer tab and click on Visual Basic

VBA VLOOKUP Example 1-2

Step 2: Once you click on Visual Basic it will open the below window for you

VBA VLOOKUP Example 1-3

Step 3: Click on Insert and select Module

VBA VLOOKUP Example 1-4

Step 4: Once you click on Module it will insert the new module for you. Double click on that newly inserted module to write your VLOOKUP code.

VBA VLOOKUP Example 1-5

Step 5: Copy and paste the below code to your newly inserted module.

Sub Vlookup_Example()
Dim rng As Range, FinalResult As Variant
Set rng = Sheets("Example 1").Range("E2")
FinalResult = Application. WorksheetFunction. VLookup(Range("D2"), Range("A2:B7"), 2, False)
rng = FinalResult
End Sub

VBA VLOOKUP Example 1-6

Let me break down the code to explain it to you.

Line 1: Dim rng as Range, FinalResult as Variant

This is the first line of the code. Using DIM statement I have declared two variables one is rng & another one is FinalResult.

Rng holds Range as the variable and FinalResult holds Variant as the variable.

Line 2: Set rng = Sheets(“Example 1”).Range(“E2”)

Previous declared variable rng is an object variable so we need to set to which range actually we are referring to. I have set the range to E2 cell. This is actually where I need the result to be displayed.

Line 3: FinalResult = Application. WorksheetFunction.VLookup (Range(“D2”), Range (“A2:B7”), 2, False)

This is the important line of the code. FinalResult holds the variable of the variant i.e. any kind of data type. FinalResugetsget the data from the function VLOOKUP.

Line 4: rng = FinalResult

Initial variable rng is equal to the value of FinalResult. Rng means E2 cell, FinalResult means value returned by the VLOOKUP function.

Step 6: Hit the RUN button or Press F5 to execute the code

VBA VLOOKUP Example 1-7

Step 7: We will get the below output.

VBA VLOOKUP Example 1-8

Example #2

Take the same data from the above example. Here I am going to create names and apply the same in the VLOOKUP. The code will the same as the previous example. Only in VLOOKUP, I am changing references.

Sub Vlookup_Example1()
Dim rng As Range, FinalResult As Variant, Table_Range As Range, LookupValue As Range
Set rng = Sheets("Example 1").Range("E2")
Set Table_Range = Sheets("Example 1").Range("A2:B7")
Set LookupValue = Sheets("Example 1").Range("D2")
FinalResult = Application. WorksheetFunction. VLookup(LookupValue, Table_Range, 2, False)
rng = FinalResult
End Sub

VBA VLOOKUP Example 2-1

Lookup value is set to D2 cell in the name of LookupValue. Table Array is set to A2 to B7 in the name of Table_Range.

Create a Macro Button

Once the macro code is written we need to design the macro.

Step 1: Go to Insert and Select the rectangular shape.

VBA VLOOKUP Example 2-2

Step 2: Draw a rectangular shape

VBA VLOOKUP Example 2-3

Step 3: Once the Rectangular shape has drawn right click on the rectangular shape and click on Assign Macro.

VBA VLOOKUP Example 2-4

Step 4: Once you click on Assign Macro it will open up all the macro names. Select the macro name you wish to assign. I am assigning Example 1 macro name to the rectangular shape.

VBA VLOOKUP Example 2-5

Step 5: Now rectangular shape has become a macro button. Once you click on the rectangular shape, you will get the below output.

VBA VLOOKUP Example 2-6

You can change the lookup value cell D2 and get the result. In the below image, I have changed the D2 cell value to Mobile.

VBA VLOOKUP Example 2-7

Things to Remember About VBA VLOOKUP

  • In VBA too VLOOKUP works the same as the worksheet function.
  • Declaring variables and assigning data type is the important thing here.
  • In the resulting cell, we do not see any kind of formula. Any value returned by VBA function does not hold any kind of formula.

Recommended Articles

This is a guide to Excel VBA VLOOKUP function. Here we discuss the VLOOKUP Formula and how to use VBA VLOOKUP in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. VBA RGB
  2. VBA Create Object
  3. VBA Solver
  4. VBA Login

All in One Software Development Bundle (600+ Courses, 50+ projects)

600+ Online Courses

50+ projects

3000+ Hours

Verifiable Certificates

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Primary Sidebar
VBA Lookup & Reference Functions
  • VBA Lookup & Reference
    • VBA Selecting Range
    • VBA Range
    • VBA Match
    • VBA Transpose
    • VBA Delete Row
    • VBA Range Cells
    • VBA Delete Column
    • VBA Union
    • VBA Insert Column
    • VBA Named Range
    • VBA Hyperlink
    • VBA Last Row
    • VBA Name
    • VBA OFFSET
    • VBA Hide Columns
    • VBA Selection Range
    • VBA Columns
    • VBA Insert Row
    • VBA LOOKUP
    • VBA VLOOKUP Function
    • VBA Resize
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

© 2020 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA Login

Forgot Password?

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more

*Please provide your correct email id. Login details for this Free course will be emailed to you
Book Your One Instructor : One Learner Free Class

Let’s Get Started

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

EDUCBA

Download VBA VLOOKUP Excel Template

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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

Special Offer - All in One Excel VBA Bundle (120+ Courses, 500+ hours of Videos) Learn More