EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home VBA VBA Resources VBA Lookup & Reference Functions VBA LOOKUP
Secondary 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
    • Compare Two Columns in Excel using VLOOKUP
    • VLOOKUP with Different Sheets

VBA LOOKUP

By Ashwani JaiswalAshwani Jaiswal

VBA Lookup

Excel VBA Lookup Function

In excel, we have used VLookup many times. It is there in Insert function which is used to fetch or map any kind of values we want. Similarly, in VBA we have Lookup application, which works as same as Excel Vlookup. VBA Lookup has a flexible data structure as it can be used to map any kind of value from any kind of table array. This means if we apply Excel Vlookup, then we won’t be able to map the right column data with left column data in one syntax. Whereas in VBA Lookup, there is no proper structure of mapping. We just need to follow the syntax of VBA Lookup. If the syntax is satisfied and properly framed then we can fetch the values from the right or left side of the table.

Syntax of VBA Lookup Function:

VBA Lookup Syntax

Where,

Watch our Demo Courses and Videos

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

  • Arg1 = Value we want to lookup.
  • Arg2 = Range of the columns or table from where we want to lookup.
  • Arg3 = Result as Boolean or Vector.

How to Use the Lookup Function in Excel VBA?

We will learn how to use the Lookup function in Excel by using the VBA Code.

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

VBA Lookup – Example #1

There are many ways to write the VBA Lookup code. In this example, we will see a simple way to write the VBA Lookup. For this, we have a data set as shown below. This table has the number of races and the average speed of the racers. Now we will be using this data to apply VBA Lookup in the below table with the Blue headers in A8 to B8. For this, follow the below steps:

VBA Lookup Example 1-1

Step 1: Open a Module from the Insert menu tab as shown below.

Insert Module

Step 2: Write the subprocedure for performed work in that Module. Here we have chosen the name as VBA Lookup.

Code:

Sub VBA_Lookup1()

End Sub

VBA Lookup Example 1-2

Step 3: Select the output cell where we need to see the lookup value. Here that cell is B9 where we will be lookup with the value concerning the name “Aniket” which is our lookup value.

Code:

Sub VBA_Lookup1()

Range("B9").Value

End Sub

VBA Lookup Example 1-3

Step 4: Now we will use Worksheet Function and select Lookup function inbuilt in the list as shown below.

Code:

Sub VBA_Lookup1 ()

Range("B9").Value = WorksheetFunction.Lookup

End Sub

VBA Lookup Example 1-4

Step 5: Once we select the Lookup function, we will see the Arg1, Arg2, and Arg3 in its syntax. For that, we will first put our Lookup value range which is cell A9 in place of Arg1.

Code:

Sub VBA_Lookup1()

Range("B9").Value = WorksheetFunction.Lookup(Range("A9").Value,

End Sub

VBA Lookup Example 1-5

Step 6: Now for Arg2, select the lookup range which is from cell A2:A5.

Code:

Sub VBA_Lookup1 ()

Range("B9").Value = WorksheetFunction.Lookup(Range("A9").Value, Range("A2:A5"),

End Sub

VBA Lookup Example 1-6

Step 7: At last, select lookup values ranges which is from B2 to B5 in place of Arg3.

Code:

Sub VBA_Lookup1()

Range("B9").Value = WorksheetFunction.Lookup(Range("A9").Value, Range("A2:A5"), Range("B2:B5"))

End Sub

values Ranges Example 1-7

Step 8: Run the code by pressing the F5 key or by clicking on the Play Button located below the menu ribbon. We will see, as per Lookup, the number of races done by the name “Aniket” is 7.

VBA Lookup Example 1-8

VBA Lookup – Example #2

There is another way to apply a Lookup function in Excel VBA. For this, we will be using the same data that we have seen in example-1. For this, follow the below steps:

All in One Financial Analyst Bundle(250+ Courses, 40+ Projects)
Financial ModelingInvestment BankingUS GAAPCFA-Level 1 & 2
Equity ResearchM & A ModelingPrivate Equity ModelingForex Trading
Price
View Courses
250+ Online Courses | 40+ Projects | 1000+ Hours | Verifiable Certificates | Lifetime Access
4.9 (86,580 ratings)

Step 1: Write the subprocedure for VBA Lookup, as shown below.

Code:

Sub VBA_Lookup2()

End Sub

VBA Lookup Example 2-1

Step 2: Define a variable as String which will be used to map the Name column.

Code:

Sub VBA_Lookup2()

Dim Name As String

End Sub

VBA Lookup Example 2-2

Step 3: In the defined variable Name, we will apply the Vlookup application as shown below.

Code:

Sub VBA_Lookup2()

Dim Name As String
Name = Application.VLookup(

End Sub

VBA Lookup Example 2-3

Step 4: Let’s say our lookup value is named “Ashwani” from the table.

Code:

Sub VBA_Lookup2()

Dim Name As String
Name = Application.VLookup("Ashwani",

End Sub

VBA Lookup Example 2-4

Step 5: And the range is from A1 to C6 from Sheet1.

Code:

Sub VBA_Lookup2()

Dim Name As String
Name = Application.VLookup("Ashwani", Sheet1.Range("A1:C6"),

End Sub

VBA Lookup Example 2-5

Step 6: Now if we want to see the average speed of rider “Ashwani” here, we need to map the cell in the Lookup syntax which is at 3rd place.

Code:

Sub VBA_Lookup2()

Dim Name As String
Name = Application.VLookup("Ashwani", Sheet1.Range("A1:C6"), 3)

End Sub

Lookup syntax Example 2-6

Step 7: Now to see the Average speed of rider “Ashwani”, we can use MsgBox and Debug Print both. But using Debug Print is way better than MsgBox. So Assign Debug Print with defined variable Name.

Code:

Sub VBA_Lookup2()

Dim Name As String
Name = Application.VLookup("Ashwani", Sheet1.Range("A1:C6"), 3)
Debug.Print Name

End Sub

Debug Print Example 2-7

Step 8: Now open Immediate Window which is there in View menu tab to see the output.

Immediate Window

Step 9: Compile the code and run it. We will see, Lookup has mapped with speed of Ashwani and fetched that into Immediate window as 86.

Code:

Sub VBA_Lookup2()

Dim Name As String
Name = Application.VLookup("Ashwani", Sheet1.Range("A1:C6"), 3)
Debug.Print Name

End Sub

VBA Lookup Example

VBA Lookup – Example #3

For using the lookup function in excel VBA, follow the below steps:

Step 1: Write the subprocedure for VBA Lookup, as shown below.

Code:

Sub VBA_Lookup3()

End Sub

VBA Lookup Example 3-1

Step 2: Declare a variable for Name as String as shown below.

Code:

Sub VBA_Lookup3()

Dim Name As String

End Sub

VBA Lookup Example 3-2

Step 3: Now assign the name which wants to lookup to defined variable Name as shown below.

Code:

Sub VBA_Lookup3()

Dim Name As String
Name = "Deepinder"

End Sub

VBA Lookup Example 3-3

Step 4: Now use any word to define and use Lookup lets say LUp. And in that use Worksheet Function with Vlookup as shown below.

Code:

Sub VBA_Lookup3()

Dim Name As String
Name = "Deepinder"
LUp = Application.WorksheetFunction.VLookup(
MsgBox "Average Speed is : " & LUp

End Sub

LUp Example 3-4

Step 5: Now use the same Vlookup syntax as we use in Excel. In Arg1 put the Name variable, then select the range matrix and look for up value which we want to get. Here that column is 3 as shown below.

Code:

Sub VBA_Lookup3()

Dim Name As String
Name = "Deepinder"
LUp = Application.WorksheetFunction.VLookup(Name, Sheet1.Range("A2:C6"), 3, False)
MsgBox "Average Speed is : " & LUp

End Sub

VLookup syntax Example 3-5

Step 6: Now use MsgBox to see the output.

Code:

Sub VBA_Lookup3()

Dim Name As String
Name = "Deepinder"
LUp = Application.WorksheetFunction.VLookup(Name, Sheet1.Range("A2:C6"), 3, False)
MsgBox "Average Speed is : " & LUp

End Sub

MsgBox Example 3-6

Step 7: Compile and run the code. We will see, for the Name “Deepinder” the Average Speed is coming as 88. Whereas the same value is given for the name Deepinder in the table.

Code:

Sub VBA_Lookup3()

Dim Name As String
Name = "Deepinder"
LUp = Application.WorksheetFunction.VLookup(Name, Sheet1.Range("A2:C6"), 3, False)
MsgBox "Average Speed is : " & LUp

End Sub

VBA Lookup Example

Pros of Excel VBA Lookup Function

  • It is as easy to use and implement as applying regular excel Vlookup formulas in Excel.
  • We can use any kind of range and matrix in VBA Lookup.
  • There are very few or no constraints while applying VBA Lookup.

Things to Remember

  • We can use Lookup in place of Vlookup in any situation.
  • Range for Lookup vector and result vector should be the same.
  • Once done the application, save the file as Macro Enable format to retain the code.
  • There is no mandatory requirement to put the result column every time to be at the right of the lookup value.

Recommended Articles

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

  1. VBA Collection
  2. VBA IF Statements
  3. VBA Sort
  4. VBA While Loop
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign In
  • Live Classes
  • 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

ISO 10004:2018 & ISO 9001:2015 Certified

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

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

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

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more