Excel VBA to Lookup Value in Range (5 Examples)

The lookup function aids in retrieving data from the primary table using a single lookup value. That sort of data structure is not necessary for the VBA LOOKUP function. Whether the result column is to the right or left of the lookup value does not matter for the LOOKUP function. Even so, it can easily retrieve the data. This article will demonstrate how to use Excel VBA to Lookup Value in Range.

You can go through a sample video of our work on VBA LOOKUP values in a range in Excel.

How to Open VBA Macro Editor in Excel

VBA is a programming language that may be used for a variety of tasks, and different types of users can use it for those tasks. Using the Alt + F11 keyboard shortcut, you can launch the VBA editor. In the last section, we will generate VBA code that makes it very easy to perform Vlookup with multiple criteria in Excel. Therefore, you can follow the simple steps accordingly to open the VBA editor.

Steps:

• Firstly, we will open the Developer tab.
• Then, we will select the Visual Basic command.

• Here, the Visual Basic window will open.
• After that, from the Insert option, we will choose the new Module to write the VBA code.

Excel VBA to Lookup Value in Range: 5 Suitable Examples

This article will demonstrate how to perform Lookup value in a range in Excel by giving 3 practical examples with explanations. Here, we will apply the VBA worksheet function, then create a UserForm to show all the criteria of an employee, and finally use HLOOKUP for ranges to lookup values in Excel VBA.

Example 1: Using VBA Worksheet Function to Lookup Value in Range with Excel VBA

In this section, we will generate the following VBA code in the Module by opening the new Module which we have discussed in the above section on how to launch the VBA Macro Editor.

Copy the following code and paste it into the above Module. After that, click on Run to see the output.

Sub Lookup_Value()
Dim ResCell As Range
Dim LookupValCell As Range
Dim LookupVec As Range
Dim ResVec As Range
Set ResCell = Range("G5")
Set LookupValCell = Range("F5")
Set LookupVec = Range("C5:C14")
Set ResVec = Range("D5:D14")
ResCell = WorksheetFunction.Lookup(LookupValCell, LookupVec, ResVec)
End Sub

Sub Lookup_Value()

Dim ResCell As Range
Dim LookupValCell As Range
Dim LookupVec As Range
Dim ResVec As Range

Set ResCell = Range("G5")
Set LookupValCell = Range("F5")
Set LookupVec = Range("C5:C14")
Set ResVec = Range("D5:D14")
ResCell = WorksheetFunction.Lookup(LookupValCell, LookupVec, ResVec)

End Sub
			

In the above video, we will demonstrate a data set where we have included some employees’ names, departments, and income. Our goal is to show the individual income of all the employees according to their designation by using the VBA Lookup function. You can follow the above video for a better understanding, as it contains all the steps.

Example 2: Using VBA Index Match Application to Lookup Value in Different Columns

Now, in this section, we will generate the following VBA code in the Module by opening the new Module which we have discussed in the above section on how to launch the VBA Macro Editor.

Copy the following code and paste it into the above Module. After that, click on Run to see the output.

Sub Find_Marks()
Dim studentID As Integer
Dim exam As String
Dim result As Variant
Dim lookupRange As Range
Dim examIndex As Variant
Dim Lookupvalue As Variant
Dim tableArray As Range
'Getting the student ID and exam name from the user
studentID = InputBox("Enter the student ID:")
exam = InputBox("Enter the exam name:")
'Setting the lookup range to be the row of the exam name (B4:F4)
Set lookupRange = Worksheets("Sheet2").Range("B4:F4")
'Finding the column index number of the exam name
examIndex = Application.Match(exam, lookupRange, 0)
'Setting the lookup value to be the student ID
Lookupvalue = studentID
'Setting the table array to be the entire range of data (B4:F7)
Set tableArray = Worksheets("Sheet2").Range("B4:F7")
'Using INDEX to find the student's marks in the exam
result = Application.Index(tableArray, Application.Match(Lookupvalue, tableArray.Columns(1), 0), examIndex)
'Showing the result in a message box
If IsError(result) Then
MsgBox "Error: Student " & studentID & " or exam " & exam & " not found in the table"
Else
MsgBox "Student " & studentID & "'s marks in " & exam & " exam is " & result
End If
End Sub

Sub Find_Marks()

    Dim studentID As Integer
    Dim exam As String
    Dim result As Variant
    Dim lookupRange As Range
    Dim examIndex As Variant
    Dim Lookupvalue As Variant
    Dim tableArray As Range

    studentID = InputBox("Enter the student ID:")
    exam = InputBox("Enter the exam name:")

    Set lookupRange = Worksheets("Sheet2").Range("B4:F4")

    examIndex = Application.Match(exam, lookupRange, 0)

   Lookupvalue = studentID
   Set tableArray = Worksheets("Sheet2").Range("B4:F7")

result = Application.Index(tableArray, Application.Match(lookupValue, tableArray.Columns(1), 0), examIndex)

End Sub

Finally, in this above video, we have demonstrated how to use lookup value with the help of a simple macro. Here, we have used the INDEX function in VBA to find out students’ marks based on the input of student ID and the name of the subject.

Example 3: Using VBA VLookup Function with If Else Statement to Lookup Value in Range in Excel VBA

Now, in this section, we will generate the following VBA code in the Module by opening the new Module which we discussed in the above section of how to launch the VBA macro editor. In this method, we will use the If..Then..Else statement for Excel VBA VLOOKUP in another worksheet.

Copy the following code and paste it into the above Module. After that, click on Run to see the output.

Sub Finding_birthplace()
'Declaring Variables
Dim ws_1 As Worksheet, ws_2 As Worksheet
Dim Finding_birthplaceAs Variant
'Setting sheets in variables
Set ws_1 = Worksheets("Birth_place")
Set ws_2 = Worksheets("VBA")
On Error Resume Next
Finding_birthplace = Application.WorksheetFunction.VLookup(ws_2.Range("B5"), _
ws_1.Range("B5:C11"), 2, False)
On Error GoTo 0
'Using If Else statement
If (IsEmpty(Finding_birthplace)) Then
ws_2.Range("E5").Formula = CVErr(xlErrNA)
Else
ws_2.Range("E5").Value = Finding_birthplace
End If
End Sub

Sub Finding_birthplace()

Dim ws_1 As Worksheet, ws_2 As Worksheet
Dim Finding_birthplaceAs Variant

Set ws_1 = Worksheets("Birth_place")
Set ws_2 = Worksheets("VBA")

On Error Resume Next

 Finding_birthplace = Application.WorksheetFunction.VLookup(ws_2.Range("B5"), _
 ws_1.Range("B5:C11"), 2, False)

On Error GoTo 0

 If Else statement
           If (IsEmpty(Finding_birthplace)) Then
                  ws_2.Range("E5").Formula = CVErr(xlErrNA)
           Else
                 ws_2.Range("E5").Value = Finding_birthplace
          End If

End Sub

In the above video, we will demonstrate a data set where we have included some writers’ names. We have introduced a Birth Place column to the author lists dataset to show you examples. We aim to find each writer’s birthplace listed in the “Birth_Place” sheet. Our goal is to show the birthplace of each writer by using the VBA VLOOKUP. So, you can follow the above video for a better understanding, as it contains all the steps.

Example 4: Creating User-Defined Function to Extract Multiple Matches in Excel VBA

Now, in this section, we will generate the following VBA code in the Module by opening the new Module which we have discussed in the above section on how to launch the VBA Macro Editor.

Copy the following code and paste it into the above Module. After that, click on Run to see the output.

Public Function Multiple_Match(ByVal LookupVal As String, ByVal RCell As Range, ByVal Colindex As Integer) As Varian
'Declare local variables
Dim Cell As Range
Dim ResultStr As String
'Use error handling to handle potential errors when accessing cell values
On Error GoTo Correction
'Loop through each cell in the given range.
For Each Cell In RCell
'Check if the cell value matches the lookup value
If Cell.Value = LookupVal Then
'Check if the corresponding column value is not empty
If Cell.Offset(0, Colindex - 1).Value <> "" Then
'Check if the value has not already been included in the result string
If Not Result_String Like "" & Cell.Offset(0, Colindex - 1).Value & "" Then
'Append the value to the result string
ResultStr = ResultStr & ", " & Cell.Offset(0, Colindex - 1).Value
End If
End If
End If
Next Cell
'Remove the leading comma and space from the result string and return it
Multiple_Match = LTrim(Right(ResultStr, Len(ResultStr) - 1))
Exit Function
'Label to jump to in case of an error
Correction:
'Return an empty string if an error occurs.
Multiple_Match = ""
End Function

Public Function Multiple_Match(ByVal LookupVal As String, ByVal RCell As Range, ByVal Colindex As Integer) As Variant

Dim Cell As Range
Dim ResultStr As String
On Error GoTo Correction

For Each Cell In RCell

  If Cell.Value = LookupVal Then
            If Cell.Offset(0, Colindex - 1).Value <> "" Then
                If Not Result_String Like "" & Cell.Offset(0, Colindex - 1).Value & "" Then
                ResultStr = ResultStr & ", " & Cell.Offset(0, Colindex - 1).Value
                End If
            End If
        End If
    Next Cell

Multiple_Match = LTrim(Right(ResultStr, Len(ResultStr) - 1))
Exit Function

Correction:
Multiple_Match = ""
End Function

Here, we have created a User-Defined function named Multiple_Match where you can extract multiple matches of sold products for different people.

Example 5: Creating an UserForm to Lookup Value in Range with Excel VBA

Here, we will create a UserForm to design our work smoothly.

Opening the VBA Editor, you can select the Insert option and click the UserForm to open it.

Then, copy the following code and paste it into the above Module. After that, click on Run to see the output.

Private Sub ComboBox1_Change()
'Declaring variable
Dim i
'Applying COUNTA function
i = Application.WorksheetFunction.CountA(Sheet2.Range("B:B"))
'Using Fir loop
For j = 1 To 4
Me("Textbox" & j).Value = Application.WorksheetFunction.VLookup(Me.ComboBox1.Value, _
ActiveSheet.Range("B" & 2, "F" & i), j + 1, 0)
Next j
End Sub
Private Sub UserForm_Initialize()
Me.ComboBox1.RowSource = "Name"
End Sub

Private Sub ComboBox1_Change()
'Declaring Variable
Dim i
'Using CountA worksheet function
i = Application.WorksheetFunction.CountA(ActiveSheet.Range("B:B"))

For j = 1 To 4
  Me("Textbox" & j).Value = Application.WorksheetFunction.VLookup(Me.ComboBox1.Value, Sheet2.Range("B" & 2, "F" & i), j + 1, 0)
Next j

End Sub

Here, we create a UserForm that contains a ComboBox and four TextBoxes. The ComboBox lists names, and when a name is selected, the TextBoxes display information associated with the name from a worksheet named “Sheet2“.  The VLOOKUP function looks up all the information with the selected name as the lookup value and the data range from column B to column F. So, for better understanding, follow this above video where we have shown the detailed process.

Things To Remember

• A substitute for VLOOKUP is the LOOKUP function.
• The result column need not always be to the right of the lookup value in order to use the LOOKUP function.
• The lookup vector and the result vector should both have the same range length.
• More crucially, the VLOOKUP function requires that we provide a column index number.

Download Practice Workbook

Conclusion

In this article, we’ve covered 3 examples of how to use Excel VBA to Lookup Value in Range. We sincerely hope you enjoyed and learned a lot from this article. Additionally, if you want to read more articles on Excel, you may visit our website, Exceldemy. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.

<< Go Back to Lookup | Formula List | Learn Excel