VBA Lookup | How to use Lookup Function in Excel VBA? (with Examples)

LOOKUP Function in Excel VBA

Lookup is the function which helps us to fetch the data from the main table based on a single lookup value. This works similar to 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. On the other hand, VBA LOOKUP function doesn’t require data structure like that, for LOOKUP function it doesn’t matter whether result column is to the right or left of the lookup value, still, it can fetch the data comfortably.

LOOKUP Function in VBA

If you already searched for the LOOKUP formula in VBA editor, I am sure you have gone mad because there is 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 LOOKUP function we need to first make use of worksheet function class.

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.

Ok, let’s write the code to apply a VBA LOOKUP function in excel.

How to Use VBA Lookup Function in Excel?

Below are the examples to use a lookup function in excel vba.

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 Excel VBA.

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

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

Code:

Sub Lookup_Example1()

Range("F3").Value

End Sub

Step 3: As I told we cannot straight away access LOOKUP function in VBA. So first use Worksheet Function class.

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.

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

Step 6: Arg 2 is our lookup vector i.e. 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

Step 7: Arg 3 is our result vector. We need the result from Avg Price column, so 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

Ok, we are done with the VBA coding.

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

Like this, we can use VBA LOOKUP function under 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 VBA 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

Even though 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.

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 VLOOKUP function.

LOOKUP Function in Excel VBA

LOOKUP Function in VBA

How to Use VBA Lookup Function in Excel?

Example #1

Example #2

Things to Remember

Recommended Articles