VBA LOOKUP

Updated on January 1, 2024
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

Excel VBA LOOKUP Function

Lookup is the function that helps us to fetch the data from the main table based on a single lookup value. VBA LOOKUP function does not require a data structure like that. For the LOOKUP function, it does not matter whether the result column is to the right or left of the lookup value. Still, it can fetch the data comfortably.

It works similarly to the VLOOKUP function, but more importantly, this is flexible in a data structure. When we say flexible in the data structure, VLOOKUPVLOOKUPThe VLOOKUP excel function searches for a particular value and returns a corresponding match based on a unique identifier. A unique identifier is uniquely associated with all the records of the database. For instance, employee ID, student roll number, customer contact number, seller email address, etc., are unique identifiers. read more 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

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA LOOKUP (wallstreetmojo.com)

Syntax

If you have already searched for the LOOKUP formula in the VBA editorVBA EditorThe Visual Basic for Applications Editor is a scripting interface. These scripts are primarily responsible for the creation and execution of macros in Microsoft software.read more, we are sure you have gone mad because there is a formula called LOOKUP in VBA.

The reason why it is not available by default is that LOOKUP is not a VBA functionVBA FunctionVBA functions serve the primary purpose to carry out specific calculations and to return a value. Therefore, in VBA, we use syntax to specify the parameters and data type while defining the function. Such functions are called user-defined functions.read more. Rather, it is a worksheet function. So to use the LOOKUP function, we must first use the worksheet function in VBAWorksheet Function In VBAThe worksheet function in VBA is used when we need to refer to a specific worksheet. When we create a module, the code runs in the currently active sheet of the workbook, but we can use the worksheet function to run the code in a particular worksheet.read more class.

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

–>> If you want to learn Excel VBA professionally, then our VBA Basic Course (16+ hours) is the perfect solution. In our Basic Excel VBA course you learn the skill of automating tasks using Variables, Data Types, Ranges, and Cells in VBA. Master Control Structures, including Conditional Statements and Loops, and discover techniques for manipulating data through sorting, filtering, and formatting. By the end of the course, you will be able to apply your acquired skills to real projects, culminating in an Excel VBA project which will solidify your ability to create efficient, automated solutions.

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,” use the following code.

Step 1: Start the subprocedure in VBASubprocedure In VBASUB in VBA is a procedure which contains all the code which automatically gives the statement of end sub and the middle portion is used for coding. Sub statement can be both public and private and the name of the subprocedure is mandatory in VBA.read more.

Code:

Sub Lookup_Example1()

End Sub
VBA Lookup Example 1.2

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

Code:

Sub Lookup_Example1()

Range("F3").Value

End Sub
VBA Lookup Example 1.3

Step 3: As we told you, we could not access the LOOKUP function immediately. So first, use the Worksheet function class.

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 do not see clear arguments of the formula. Here, Arg 1 means lookup value. Our lookup value is in 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

We have completed the VBA codingVBA CodingVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more.

Let us run the code. First, 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 at results. Below is the example code of the 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. Therefore, instead of directly supplying all the values to the formula, we have declared variables and assigned them a set of ranges and values.

Lookup - Output 2

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

Things to Remember

Recommended Articles

This article has been a guide to VBA Lookup. Here, we discuss using the VBA lookup function, examples, and a downloadable Excel template. You can learn more about VBA from the following articles: –