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.
Download Practice Workbook
You may download the following Excel workbook for better understanding and practice it yourself.
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
VBA Breakdown:
- Firstly, this code starts with the Sub statement and the name of the subroutine, “Lookup_Value()“.
Sub Lookup_Value()
- Now we are Introducing Variables: The cell where the lookup result will be shown is stored in the variable ResCell As Range. LookupValCell As Range: This variable keeps the lookup value’s cell in it. LookupVec As Range: This variable stores the lookup vector’s range. ResVec As Range: The result vector’s range is kept in this variable.
Dim ResCell As Range
Dim LookupValCell As Range
Dim LookupVec As Range
Dim ResVec As Range
- Each variable is assigned to the appropriate cell or range using the Set command. The lookup is carried through using the Lookup function. The lookup value is represented by the LookupValCell variable, the lookup vector by the LookupVec variable, and the result vector by the ResVec variable. The ResCell variable holds the lookup’s outcome.
Set ResCell = Range("G5")
Set LookupValCell = Range("F5")
Set LookupVec = Range("C5:C14")
Set ResVec = Range("D5:D14")
ResCell = WorksheetFunction.Lookup(LookupValCell, LookupVec, ResVec)
- Finally, this VBA macro ends with an End statement.
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
VBA Breakdown:
- Firstly, this code starts with the Sub statement and the name of the subroutine, “Find_Marks()“.
Sub Find_Marks()
- The code begins by declaring a number of variables, including studentID, exam, result, lookupRange, examIndex, Lookupvalue, and tableArray. Exam is an integer, while result is a text. (a range).
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
- The student ID and exam name must be entered into the InputBox function by the user.
studentID = InputBox("Enter the student ID:")
exam = InputBox("Enter the exam name:")
- The lookupRange variable is set to the table row containing the exam names.
Set lookupRange = Worksheets("Sheet2").Range("B4:F4")
- The Match function is used to set the examIndex variable to the provided exam’s column index.
examIndex = Application.Match(exam, lookupRange, 0)
- The user-entered student ID is used as the value for the Lookupvalue variable. The full table’s data range is contained in the tableArray variable.
Lookupvalue = studentID
Set tableArray = Worksheets("Sheet2").Range("B4:F7")
- Now, using the lookupValue and examIndex variables, the Index function searches the table for the student’s grades.
result = Application.Index(tableArray, Application.Match(lookupValue, tableArray.Columns(1), 0), examIndex)
- Finally, this VBA macro ends with an End statement.
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
VBA Breakdown:
Sub Finding_birthplace()
- Firstly, this VBA macro begins with the Sub procedure Finding_birthplace, which is used to locate the birthplace of a person by searching for their name in a table.
Dim ws_1 As Worksheet, ws_2 As Worksheet
Dim Finding_birthplaceAs Variant
- Secondly, we use the Dim statement is used to declare three variables: ws_1 and ws_2 as Worksheet objects, and Finding_birthplace as a Variant data type.
Set ws_1 = Worksheets("Birth_place")
Set ws_2 = Worksheets("VBA")
- Now, we apply the Set statement to assign the Birth_place and VBA worksheets to the ws_1 and ws_2 variables, respectively.
On Error Resume Next
- Then, by using the On Error Resume Next statement, we ignore any runtime errors that may occur during the implementation of the macro.
Finding_birthplace = Application.WorksheetFunction.VLookup(ws_2.Range("B5"), _
ws_1.Range("B5:C11"), 2, False)
- After that, we set the Finding_birthplace variable to assign the result of the VLOOKUP function, which finds the value in cell B5 of the VBA worksheet in the table located in the Birth_place worksheet’s B5:C11 range. The function returns the value in the second column of the table (column C) that corresponds to the matching value in column B.
On Error GoTo 0
- Here, we apply the, On Error GoTo 0 statement to turn off the error handling.
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
- Therefore, we apply the If Else statement to check if the Finding_birthplace variable is empty. If it is, the code assigns the #N/A error value to cell E5 of the VBA worksheet using the CVErr function. If it is not empty, the code assigns the value of the Finding_birthplace variable to cell E5.
End Sub
- Finally, the Sub procedure ends with the End Sub statement.
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
VBA Breakdown:
- This code creates a VBA method called Multiple_Match that accepts three arguments: Colindex, LookupVal, and RCell. (an integer). A comma-separated list of values in the column designated by the Colindex will be returned after this function has searched for instances of the LookupVal within the RCell range.
Public Function Multiple_Match(ByVal LookupVal As String, ByVal RCell As Range, ByVal Colindex As Integer) As Variant
- First, Cell (a range) and ResultStr are declared as local variables. (a string). Then it configures error handling to detect any mistakes that might happen when accessing cell values.
Dim Cell As Range
Dim ResultStr As String
On Error GoTo Correction
- The code then uses a For Each loop to iterate through each cell in the RCell range.
For Each Cell In RCell
- It determines whether the value in each cell corresponds to the LookupVal. The function determines whether the relevant column value (defined by the Colindex) is not empty if the value in the cell matches the LookupVal. The function checks to see if the value hasn’t previously been used in the ResultStr if the column value isn’t empty. The function appends the value to the ResultStr string variable if it hasn’t already been there, otherwise it removes it.
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
- The function checks every cell in the RCell range before removing the leading comma and space from the ResultStr and returning it as the function’s output value.
Multiple_Match = LTrim(Right(ResultStr, Len(ResultStr) - 1))
Exit Function
- The output value is set to an empty string if an error occurs while the function is being executed, and the error handling jumps to the Correction label.
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
VBA Breakdown:
Private Sub ComboBox1_Change()
'Declaring Variable
Dim i
'Using CountA worksheet function
i = Application.WorksheetFunction.CountA(ActiveSheet.Range("B:B"))
- Firstly, we will define the first sub procedure “ComboBox1_Change()”. Secondly, we declare a variable “i“. Then, it counts the number of cells that contain data in column B of ActiveSheet using the COUNTA worksheet function and assigns the value to “i“.
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
- The For loop runs for four iterations. Each iteration uses the VLOOKUP function to look up the selected name in the data range. It returns the value in the column that corresponds to the current iteration plus one (starting with column C). The value is then assigned to the corresponding TextBox (named “Textbox1” to “Textbox4“).
-
Private Sub UserForm_Initialize() Me.ComboBox1.RowSource = "Name"
After that, we will define a sub-procedure “UserForm_Initialize()“.
- It sets the RowSource property of the ComboBox to “Name“, which means that the ComboBox gets its list of names from a named range called “Name“.
End Sub
- Finally, this VBA macro ends with an End statement.
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.
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.
Hi Bishawajit
This is useful function up until now.
“=LOOKUP(2,1/($A$2:$A$19=D2),$B$2:$B$19)”
However, I am looking for a function or formula that can be added.
This is to look up only a cell with value (ignoring or skipping all with any blank cell from bottom to top row).
Thanks for your great help.
Regards,
Nats
Hello NATS CO
Thanks for reaching out and posting your comment. You have given us wonderful advice, which is very appreciated. You want to modify the existing formula you have given in such a way that it will look up only a cell with value and ignore all blank cells from bottom to top.
I am delighted to inform you that I have developed such a formula by modifying your given formula. I have also developed a User-defined function using Excel VBA.
Modified Excel Formula:
Excel VBA User-defined Function:
Open the VBA Editor => Paste the following code in a module => Save.
Return to the sheet => Use the user-defined function like other worksheet functions.
I hope the idea will fulfil your requirements. Good luck!
Regards
Lutfor Rahman Shimanto