WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA LOOKUP

VBA LOOKUP

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

By Jeevan A Y

Excel VBA LOOKUP Function

Lookup is the function which helps us to fetch the data from the main table based on a single lookup value. VBA LOOKUP function doesn’t require data structure like that, for LOOKUP function it doesn’t matter whether the result column is to the right or left of the lookup value, still, it can fetch the data comfortably.

This works similarly to the VLOOKUP function, but more importantly, this is flexible in a data structure. When we say flexible in the data structure, it means VLOOKUP needs all the values to the right of a lookup value in the main table, and anything left to the lookup value VLOOKUP cannot find it.

VBA-Lookup

Syntax

If you already searched for the LOOKUP formula in VBA editor, I am sure you have gone mad because there is a formula called LOOKUP in VBA.

The reason why it is not available by default because LOOKUP is not a VBA function rather, it is a worksheet function. So to use the LOOKUP function, we need to first make use of the worksheet function in VBA class.

VBA Lookup Syntax

  • Arg 1 means lookup value
  • Arg 2 is our lookup vector, i.e., range of our lookup value column
  • Arg 3 is our result vector.

How to Use VBA Lookup Function? (Examples)

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

Example #1

We have the following data from which we want to get the Avg Price of a particular product name using the Lookup function in VBA Excel.

VBA Lookup Example 1.1

To get the Avg Price of Mobile Accessories to use the following code.

Step 1: Start the subprocedure in VBA.

Code:

Sub Lookup_Example1()

End Sub

VBA Lookup Example 1.2

Step 2: I need the result in the F3 cell. So my code is Range(“F3”).Value =.

Code:

Sub Lookup_Example1()

Range("F3").Value

End Sub

VBA Lookup Example 1.3

Step 3: As I told, we could not straight away access the LOOKUP function. So first, use the Worksheet Function class.

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

VBA Lookup Example 1.4

Step 4: The moment we apply the Worksheet Function class, we can see all the available formulas with this class in VBA, so select LOOKUP from this list.

Example 1.5

Step 5: With worksheet functions in VBA, we don’t see clear arguments of the formula. Here Arg 1 means lookup value. Our lookup value is in the cell E3, so write the code as Range(“E3”).Value.

Code:

Sub Lookup_Example1()

Range("F3").Value = WorksheetFunction.Lookup(Range("E3").Value

End Sub

Example 1.6

Step 6: Arg 2 is our lookup vector, i.e., the range of our lookup value column. In this example, our lookup vector range is from B3 to B10.

Code:

Sub Lookup_Example1()

Range("F3").Value = WorksheetFunction.Lookup(Range("E3").Value,Range("B3:B10"),

End Sub

Example 1.7

Step 7: Arg 3 is our result vector. We need the result from the Avg Price column, so the result vector is from Range C3 to C10.

Code:

Sub Lookup_Example1()

Range("F3").Value = WorksheetFunction.Lookup(Range("E3").Value, Range("B3:B10"), Range("C3:C10"))

End Sub

Example 1.8

Ok, we are done with the VBA coding.

Let’s run the code, and we should get the result in cell F3.

Lookup - Output 1

Like this, we can use the LOOKUP function under the worksheet function class to fetch the data of the supplied lookup value.

Example #2

Use Variables for LOOKUP Function

Using variables also we can arrive results. Below is the example code of lookup function with variables.

Code:

Sub Lookup_Example2()

    Dim ResultCell As Range
    Dim LookupValueCell As Range
    Dim LookupVector As Range
    Dim ResultVector As Range

    Set ResultCell = Range("F3")
    Set LookupValueCell = Range("E3")
    Set LookupVector = Range("B3:B10")
    Set ResultVector = Range("C3:C10")

ResultCell = WorksheetFunction.Lookup(LookupValueCell, LookupVector, ResultVector)

End Sub

Example 2.1

Even though it looks lengthy, it will give good exposure to variables in VBA. Instead of supplying all the values to the formula directly, we have declared variables and assigned set of ranges and values to them.

Lookup - Output 2

Declaring variables and setting the reference to those variables is always a good practice in any coding language.

Things to Remember

  • LOOKUP is an alternative function to VLOOKUP.
  • LOOKUP not necessarily requires the result column to be at the right of the lookup value.
  • The range length of both the lookup vector and the result vector should be the same.
  • More importantly, we need to supply a column index number, which is necessary for the VLOOKUP function.

Recommended Articles

This has been a guide to VBA Lookup. Here we discuss how to use the VBA lookup function along with examples and a downloadable excel template. You can learn more about VBA from the following articles –

  • VBA Month
  • Pivot Table VLOOKUP
  • VLookup in VBA
  • VLOOKUP with Match
0 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 >>

Category iconExcel, VBA & Power BI,  Learn VBA

Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. 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

WallStreetMojo

Free Excel Course

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

* 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
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

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

WallStreetMojo

Free Excel Course

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

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

Login

Forgot Password?

WallStreetMojo

Download VBA Lookup Excel Template

Special Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More