VBA LOOKUP

The LOOKUP function in Excel VBA is used to find a value in a range and return a corresponding value from another range. It searches from top to bottom or left to right, depending on the data layout. The data must be sorted in ascending order. It is used with the WorksheetFunction object in VBA.

Example:

MsgBox Application.WorksheetFunction.Lookup(2, Array(1, 2, 3), Array("One", "Two", "Three"))

This shows "Two" because 2 matches the second item in the first array.

It works similarly to the VLOOKUP function, but more importantly, it is flexible in a data structure. It means that 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.

If you search for the LOOKUP formula in the VBA editor, 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 must first use the worksheet function in VBA class.

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

Here’s how we can  use the VBA LOOKUP function step by step with a simple example:

Step 1: Open Excel and press Alt + F11 to open the VBA editor.

Step 2: Insert a new module by clicking Insert -> Module.

Step 3: Type the following code to use the LOOKUP function:

Sub SimpleLookup()

    Dim result As String

    result = Application.WorksheetFunction.Lookup(3, Array(1, 2, 3, 4), Array("A", "B", "C", "D"))

    MsgBox result

End Sub

Step 4: This code looks for the value 3 in the first array and returns the corresponding value "C" from the second array.

Step 5: Press F5 to run the macro and see the result in a message box.

Examples

The below examples help demonstrate how the VBA LOOKUP function works in real situations. 

Example #1

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

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

Step 1: Start the subprocedure in VBA.

Code:

Sub Lookup_Example1()

End Sub

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

Step 3: As mentioned earlier, we can't access the LOOKUP function immediately. So first, use the 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 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

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

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

We have completed the VBA coding.

Let us run the code. First, we should get the result in cell F3.

Likewise, 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

One can use variables in VBA LOOKUP function to make the code more flexible. Assign values to the variables and pass them into the function instead of hardcoding them.

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

Step 1: Even though it looks lengthy, it helps us understand variables in VBA. Instead of directly supplying all the values to the formula, we have declared variables and assigned them a set of ranges and values.

This code looks up the value in cell E3 within the range B3:B10 and returns the corresponding value from C3:C10 into cell F3. 

Make sure the lookup range is sorted in ascending order for the LOOKUP function to work correctly.

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

• The LOOKUP function is an alternative function to VLOOKUP.
• The LOOKUP function does not necessarily require 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.

Find VBA very interesting? You can go through this Excel VBA Basic and Advanced Course and gain expertise as mentioned in the feedback of many of our users.