Microsoft Excel allows us to estimate different types of data and carry out monetary, mathematical, and statistical computations. When it comes to augmenting output at work or gaining time, macros are your ultimate guide. The VBA coding language allows us to automate any process, no matter how small or large. VBA stands for Visual Basic for Applications which is the programming language for Office applications such as MS Excel, MS Word, and MS Access. It enhances Excel’s functionality by allowing you to control its behavior. In this article, we will show you how to apply Excel VBA code to look for multiple matches in a dataset. Below is an overview of the article where I will demonstrate how to Vlookup values for multiple matches using Excel VBA.
How to Launch VBA Editor in Excel
Before going to methods, in this part, we will show you how to open the Visual Basic Tab. This is the environment where we write our code. To open the Visual Basic Tab, follow the below steps.
📌 Steps:
- First, go to the Developer tab in the ribbon.
- Then, click on Visual Basic.
- Consequently, the Visual Basic window will be opened.
- Or, you can press ALT+F11 from your keyboard to open Visual Basic.
- After that, in the Visual Basic tab, click on Insert and select the Module option.
- As a result, a coding module will appear.
Note:
If you can’t find the Developer tab in the Excel ribbon or if you are working for the very first time with VBA macros, don’t panic, you can get the Developer tab easily in your ribbon. It is not displayed in the ribbon by default. You can follow the article on how to display the Developer tab on the ribbon.
Excel VBA to Vlookup Multiple Matches: 2 Effective Ways
In order to demonstrate how you can Vlookup values for multiple matches using VBA, I have chosen the following 2 approaches. To do that I have taken a dataset of 11 rows and 3 columns which are Rep Name, Item, and Units. In this article, we are going to use Excel 365 version. You can use any other version according to your convenience.
1. Excel VBA Code to Vlookup Values for Multiple Matches
In this part, I have 3 different sub-procedures to look up values for multiple matches from a dataset. You can use any of them as per your choice.
1.1. Return Multiple Values for Matches in One cell with Separators in Between with VBA
First, I have attached a demonstrative video here for your better understanding.
We want to find out the Items associated with different Representative Names. Suppose we want to find the Items correlated with Jones.
📌 Steps:
- Go to the Developer tab and click Visual Basic to open the editor window.
- Or, you can press ALT+F11 from your keyboard to open Visual Basic.
- Click on Insert and select the Module option to open a new module.
- Now insert the following code in the code editor and press F5 to run the entire code.
Sub SearchItemsByRep()
Dim search_rep As String
Dim search_range As Range
Dim cell As Range
Dim result As String
'Prompt user to enter search rep name
search_rep = InputBox("Enter Representative Name to search for:")
If search_rep = "" Then Exit Sub 'Exit sub if user cancels or enters nothing
Range("B17").Value = search_rep
'Specify search range
Set search_range = Range("B4:C14") 'replace with your own range
'Loop through each cell in range and concatenate results
For Each cell In search_range.Cells
If cell.Value = search_rep Then
result = result & cell.Offset(0, 1).Value & ", "
End If
Next cell
'Remove trailing comma and space
result = Left(result, Len(result) - 2)
'Output results to cell C17
Range("C17").Value = result
End SubCode Breakdown
Sub SearchItemsByRep()- This line starts the subroutine named “SearchItemsByRep“.
Dim search_rep As String
Dim search_range As Range
Dim cell As Range
Dim result As String- These lines declare four variables: search_rep as a string to hold the representative name being searched for, search_range as a range object to hold the range of cells to search, cell as a range object to represent the current cell in the search range and result as a string to hold the concatenated search result.
search_rep = InputBox("Enter Representative Name to search for:")- This line prompts the user to enter the search rep name to search for, using the InputBox function, and assigns the entered value to the search_rep variable.
If search_rep = "" Then Exit Sub - This line checks if the user has entered a representative name or if the input box was canceled. If we enter an empty value or the input box is canceled, the subroutine is exited using the Exit Sub statement.
Set search_range = Range("B4:C14")- In this part of the code, we specify the search range which is B4:C14. You can replace it with your range.
For Each cell In search_range.Cells
If cell.Value = search_rep Then
result = result & cell.Offset(0, 1).Value & ", "
End If
Next cell- In this part of the code, we use a For Each loop to iterate through each cell in the search_range variable. If the current cell value matches the search_rep variable then it concatenates the item value in the current cell.
result = Left(result, Len(result) - 2) This removes trailing commas and spaces.
Range("C17").Value = result
This shows the output results to cell C17.
1.2. Return Multiple Values for Matches in Separate Cells Vertically with VBA
Here is a demonstrative video of the whole approach for your better understanding:
In this approach, we want the same result but in a different way. We want to get the output in different cells and also vertically with no separators between them. To get that follow the below part:
📌 Steps:
- Insert a new module by clicking Insert and then Module.
- A new module named Module2 will be created.
- Now, insert the following code in that new module and press F5 from your keyboard to run the entire code.
Sub SearchItemsByRep()
Dim search_rep As String
Dim search_range As Range
Dim cell As Range
Dim result As String
Dim output_cell As Range
'Prompt user to enter search rep name
search_rep = InputBox("Enter Resentative Name to search for:")
If search_rep = "" Then Exit Sub 'Exit sub if user cancels or enters nothing
'Specify search range
Set search_range = Range("B4:C14") 'replace with your own range
'Initialize output cell to show output
Set output_cell = Range("G5")
'Loop through each cell in range and concatenate results
For Each cell In search_range.Cells
If cell.Value = search_rep Then
output_cell.Value = cell.Offset(0, 1).Value
Set output_cell = output_cell.Offset(1, 0)
End If
Next cell
End SubCode Breakdown
Sub SearchItemsByRep()- This line starts the subroutine named “SearchItemsByRep“.
Dim search_rep As String
Dim search_range As Range
Dim cell As Range
Dim result As String
Dim output_cell As RangeThese lines declare five variables: search_rep, search_range, cell, result, output_cell
search_rep = InputBox("Enter Representative Name to search for:")- This line prompts the user to enter the search rep name to search for, using the InputBox function, and assigns the entered value to the search_rep variable.
If search_rep = "" Then Exit SubExit sub if the user cancels or enters nothing
Set search_range = Range("B4:C14")Specifying the search range.
Set output_cell = Range("G5")Initializing output cell to show output.
For Each cell In search_range.Cells
If cell.Value = search_rep Then
output_cell.Value = cell.Offset(0, 1).Value
Set output_cell = output_cell.Offset(1, 0)
End If
Next cellIn this part of the code, we use a For Each loop to iterate through each cell in the search_range variable. If the current cell value matches the search_rep variable then it concatenates the item value in the current cell.
Read More: Excel VBA Vlookup with Multiple Criteria
1.3. Return Multiple Values for Matches in Separate Cells Horizontally with VBA
Here is a demonstrative video of the whole approach for your better understanding:
In this 3rd approach, we want the output in different cells horizontally. Here are the steps to follow.
📌 Steps:
- Insert another new module by clicking Insert and then Module.
- A new module named Module3 will be created.
- Now, insert the following code in that new module and press F5 to run the entire code.
Sub SearchItemsByRep()
Dim search_rep As String
Dim search_range As Range
Dim cell As Range
Dim result As String
Dim output_cell As Range
'Prompt user to enter search rep name
search_rep = InputBox("Enter representative name to search for:")
If search_rep = "" Then Exit Sub 'Exit sub if user cancels or enters nothing
'Specify search range
Set search_range = Range("B4:C14") 'replace with your own range
'Initialize output cell to show output
Set output_cell = Range("C17")
'Loop through each cell in range and concatenate results
For Each cell In search_range.Cells
If cell.Value = search_rep Then
output_cell.Value = cell.Offset(0, 1).Value
Set output_cell = output_cell.Offset(0, 1)
End If
Next cell
End Sub
2. Excel VBA to Create a User Defined Function to Vlookup for Multiple Matches
One drawback of the Excel VLOOKUP function is that if multiple values match, the function will return the first matched record. So when we have such a dataset with multiple matched values, we can’t use the VLOOKUP function in a previous way. In this part, I will show you how to create a user-defined function that extracts multiple matched values from the dataset.
2.1. User-Defined Function to Vlookup for Multiple Values From Dataset
Here is a demonstrative video of this approach for your better understanding:
To get multiple lookup values in a single cell, we need to create a function in VBA (similar to the VLOOKUP function) that checks each cell in a column and adds the lookup value if found. I have attached the VBA code below.
Now, insert the following code in a new module and press F5 to run the entire code.
Public Function Vlookup_Multimatch(ByVal Lookup_Value As String, ByVal Cell_Range As Range, ByVal Column_Index As Integer) As Variant
'Declare a variable to hold the current cell in the loop
Dim cell As Range
'Declare a string variable to hold the matching values
Dim Result_String As String
'Define the error handling routine
On Error GoTo Error
'Loop through each cell in the specified range
For Each cell In Cell_Range
'Check if the current cell matches the lookup value
If cell.Value = Lookup_Value Then
'Check if the value in the specified column is not empty
If cell.Offset(0, Column_Index - 1).Value <> "" Then
'Check if the value is not already in the result string
If Not Result_String Like "*" & cell.Offset(0, Column_Index - 1).Value & "*" Then
'Add the value to the result string
Result_String = Result_String & ", " & cell.Offset(0, Column_Index - 1).Value
End If
End If
End If
Next cell
'Trim leading spaces and return the result string
Vlookup_Multimatch = LTrim(Right(Result_String, Len(Result_String) - 1))
'Exit the function
Exit Function
'Define the error handling routine
Error:
'Return an empty string if an error occurs
Vlookup_Multimatch = ""
End Function
Code Breakdown
Public Function Vlookup_Multimatch(ByVal Lookup_Value As String, ByVal Cell_Range As Range, ByVal Column_Index As Integer) As VariantThis function takes a lookup value, a range of cells, and a column index and returns a concatenated string of all matching values in the specified column.
Dim cell As Range Declare a variable to hold the current cell in the loop.
Dim Result_String As String Declare a string variable to hold the matching values.
On Error GoTo Error Define the error handling routine.
We have used a nested For Each loop here. The function uses a loop to iterate through each cell in the specified range. If the cell’s value matches the lookup value, it checks if the corresponding cell in the specified column is not empty. If it is not empty and the cell’s value is not already included in the result string, it adds the cell’s value to the result string.
The function returns the result string as a variant data type. If there are no matches, the function returns an empty string.
Note:
You have to remember that this function can only handle exact matches between the lookup value and the cell values in the specified range. If you need to perform a partial or fuzzy match, you will need to modify the function or use a different method.
This function takes 3 arguments just like the VLOOKUP function: Lookup value, LookupRange, and ColumnNumber.
Here is the final output image. Take a look at it.
2.2. Creating VBA User-Defined Function to Get Multiple Lookup Values in a Single Cell (Without Repetition)
The data may contain repetitions. To get the result where there are no repetitions, we will need to modify the code a little bit.
Note:
Note that, to make this understandable to you, we have made a little bit of change in our dataset. In cell C14 we have inserted the Item “Pencil” instead of “Sharpener” so that you can understand the scenario clearly.
Here is a demonstrative video of this approach for your better understanding:
I have attached the VBA code below. Follow this to get the result.
Now, insert the following code in a new module and press F5 from your keyboard to run the entire code.
Public Function Vlookup_Multimatch_NoRept(Lookup_Value As String, Lookup_Range As Range, Column_Number As Integer)
'Declare variable i as a long data type
Dim i As Long
'Declare variable result as a string data type
Dim result As String
'Loop through each cell in column 1 of Lookup_Range
For i = 1 To Lookup_Range.Columns(1).Cells.Count
'If the current cell in column 1 of Lookup_Range matches Lookup_Value
If Lookup_Range.Cells(i, 1) = Lookup_Value Then
'Loop through each cell in column 1 of Lookup_Range before the current cell
For j = 1 To i - 1
'If a previous cell in column 1 of Lookup_Range matches Lookup_Value
If Lookup_Range.Cells(j, 1) = Lookup_Value Then
'If the value in column Column_Number for the previous cell matches the value in column Column_Number for the current cell
If Lookup_Range.Cells(j, Column_Number) = Lookup_Range.Cells(i, Column_Number) Then
GoTo Error 'Go to the Error label and skip the next line of code
End If
End If
Next j
'Add the value in column Column_Number for the current cell to the result string with a comma separator
result = result & " " & Lookup_Range.Cells(i, Column_Number) & ","
Error:
End If
Next i
'Return the result string with the last comma removed
Vlookup_Multimatch_NoRept = Left(result, Len(result) - 1)
End FunctionCode Breakdown
Public Function Vlookup_Multimatch_NoRept(Lookup_Value As String, Lookup_Range As Range, Column_Number As Integer)This function takes three arguments: Lookup_Value, Lookup_Range, and Column_Number. Lookup_Value is the value you want to search for in Lookup_Range, Lookup_Range is the range of cells you want to search for Lookup_Value in, Column_Number is the column number within Lookup_Range that you want to return a value from.
Dim i As Long Declare variable i as a long data type
Dim result As String Declare variable result as a string data type
For i = 1 To Lookup_Range.Columns(1).Cells.Count Loop through each cell in column 1 of Lookup_Range
If Lookup_Range.Cells(i, 1) = Lookup_Value Then If the current cell in column 1 of Lookup_Range matches Lookup_Value
For j = 1 To i - 1 Loop through each cell in column 1 of Lookup_Range before the current cell
If Lookup_Range.Cells(j, 1) = Lookup_Value Then If a previous cell in column 1 of Lookup_Range matches Lookup_Value
If Lookup_Range.Cells(j, Column_Number) = Lookup_Range.Cells(i, Column_Number) ThenIf the value in column Column_Number for the previous cell matches the value in column Column_Number for the current cell
GoTo Error It goes to the Error label and skips the next line of code
Next jMoves to the next cell in column 1 of Lookup_Range
result = result & " " & Lookup_Range.Cells(i, Column_Number) & "," Adds the value in column Column_Number for the current cell to the result string with a comma separator
Error: This is defined Error label
Next iMoves to the next cell in column 1 of Lookup_Range
Vlookup_Multimatch_NoRept = Left(result, Len(result) - 1) Returns the result string with the last comma removed.
Here is the final output image of the whole approach. Take a look at it.
Read More: How to Use Excel VBA VLookup with Named Range
How to Vlookup for Multiple Matches with Formula in Excel
Without using VBA, you can also find matched values from the dataset by applying the Excel built-in VLOOKUP function. The VLOOKUP function is one of the most commonly used functions in Microsoft Excel. The purpose of this part is to show you, aside from using VBA code, how to VLOOKUP multiple matches in Excel using the VLOOKUP formula.
The simplest method is indeed this one. In this case, we’ll only be using the FILTER function. We want to find the Items associated with the representative name Thompson. Here are the steps you need to follow.
First, enter the Rep Name in cell F5 for whom you want to find the Items. In our case, it is Thompson. Enter the following formula in cell G5 and press the ENTER key.
Formula Breakdown
- $C$5:$C$14 (Item) is the lookup_array. We are looking for the items. You can use your one as well.
- $B$5:$B$14 (Rep Name) is the matching_array. We want to match our selected value(Thompson) with this Matching_array to find out the associated Items. You can use your one accordingly.
- $F$5(Rep Name) is the matching_value. We want to match this value with matching_array.
Here, we have the Items Binder and Stapler which are correlated to our selected Rep Name Thompson. Using this method, you can also easily find matched values without using VBA code.
How to Use VLOOKUP Function with Multiple Criteria in Excel
We can use the VLOOKUP function to find values with multiple criteria in a table to extract its corresponding information. In this part of the article, we will demonstrate the steps to Vlookup values with multiple criteria using the VLOOKUP function.
We have to use the IF function here to define the lookup array for the VLOOKUP function. Follow these steps to complete the process.
First, enter the criteria which are Rep Name and Item in cells F5 and G5 accordingly. We want to find the Units based on these two criteria. Enter the following formula in cell H5 and press the ENTER key.
Here, we have got Unit “83” based on the criteria Rep Name “Thompson” and Item “Stapler”. In this way, you can combine the IF function with the VLOOKUP function to find matched values based on multiple criteria.
Read More: Excel VBA: Working with If, IsError, and VLookup Together
Frequently Asked Questions
1. The Developer Tab Is Not Visible. What to Do?
If you can’t find the Developer tab in your Excel don’t panic. You can follow the above note part of “How to Launch VBA Editor in Excel”. I hope now you got your solution.
2. Can I Use a Macro for VLOOKUP?
You can make your work faster by using macros. If you want to increase your productivity or gain more time at work, macros are your best friend. Any process can be automated using the VBA coding language.
Here is an example of using a macro to perform a specific task easily.
In the future, I hope you’ll make use of macros in your work and enjoy the use of VBA macros. It will simplify your work and speed up the process.
3. What is a Substitute for VLOOKUP in Excel?
Yes, you can use Excel INDEX and MATCH functions to Vlookup matched values as a substitute for VLOOKUP. For a better understanding, you can go through the mentioned article in ExcelDemy.
4. Can We Have Multiple Conditions in VLOOKUP?
You can surely use the VLOOKUP function when you have multiple conditions to apply to your dataset. I hope the attached article will give you a better and clearer understanding of using VLOOKUP in multiple conditions.
Key Takeaways from the Article
- In this article, I have shown how to launch VBA editor in Excel
- Chosen real-life dataset for better understanding.
- Focusing on how to Vlookup multiple matched values with VBA code in Excel.
- Explained how to create a user-defined function for Vlookup values.
- Explained different approaches with VBA code.
- Provide solutions to frequently asked questions of readers.
- Overall focused on using VBA code to Vlookup values from a dataset.
Download Practice Workbook
You can download the practice workbook from here:
Conclusion
In this article, we have covered how we can Vlookup for multiple matches from our dataset using VBA code in Excel. It will ease our work and speed up our productivity. I hope you enjoyed this article and learned a lot from it. Feel free to leave any questions, comments, or recommendations in the comment section.
Further Readings
- Use Excel VBA VLOOKUP to Find Values in Another Worksheet
- How to Use Excel VBA VLookup Within Loop
- Excel VBA to Vlookup in Another Workbook Without Opening
