Excel VBA to Find Matching Value in Column (8 Examples)

This article will show you how to use Excel VBA to Find Matching Value in Column. With VBA, you can automate the process of searching for specific values or patterns within a dataset, and filter the results based on your desired criteria. This can save a lot of time and effort, especially when working with large datasets. In this article, we will explore various techniques and functions that can be used in Excel VBA to Find Matching Value in Column.

Whether you are new to VBA or an experienced user, this article will provide you with practical insights and tips on how to find matching values in columns in Excel VBA. The following video shows the overview of using the VBA Match function to find the matching value in Excel.

Download Practice Workbook

You can download the Excel workbook that we have used to prepare this article.

How to Launch VBA Editor in Excel

In this section, we are going to demonstrate how to create a VBA module in Excel. First, you need the Developer tab to display on your ribbon. If you don’t have that, you can look for it in how to enable the Developer tab on your ribbon.

• First, we go to the Developer tab.
• Then we will select Visual Basic.

Then a new window will pop up. Then we are going to follow these steps.

• First, we will select Insert.
• Then we are going to select Module.
• A new Module will be created.

Excel VBA to Find Matching Value in Column: 8 Examples

The main idea behind finding a matching value in a column or a range is to use the Match function from the WorksheetFunction object. We can also achieve the same result by utilizing Range.Find method in VBA. More details to follow in sections dedicated to each case.

Here is the dataset for the article for showing Matching Value in a column. We have given 8 examples to find out the matching value in VBA.

1. Using Match Function to Find One Matching Value in Column

In this section, we are going to find the Match value of the Model according to order ID.

• First, we have prepared some cells for Order ID and Model in range H4:I5.

• Then we have given the code in Module.

• You can copy the code from here.

Sub OneMatch_Value()
    Dim modelRange As Range
    Set modelRange = Range("D5:D12")
    Range("I5").Value = WorksheetFunction.Index(modelRange, WorksheetFunction.Match(Range("H5").Value, Range("F5:F12"), 0))
End Sub

• Simply running it will yield the result if you insert the order id beforehand. However, we are going to assign it to a Command Button.

• After Creating a button, we are going to name it “Search” and assign the Macro. You can do that by right-clicking on the button and selecting Assign Macro from the context menu and selecting the proper code through the sub name.

• After clicking the “Search” button we get the final output.

Sub OneMatch_Value()
    Dim modelRange As Range
    Set modelRange = Range("D5:D12")

Range("I5").Value = WorksheetFunction.Index(modelRange, WorksheetFunction.Match(Range("H5").Value, Range("F5:F12"), 0))

2. Utilizing Match Function to Find Multiple Matching Values in Column

• First, you will give the Order Id in the H column and select the I column for Model.
• Now you have to create a module and write the following code.

• You can copy the code from here.

Sub MutipleMatch_Value()
    Dim i As Integer
    Dim modelRange As Range
    Set modelRange = Range("D5:D12")
    For i = 5 To 7
    Cells(i, 9).Value = WorksheetFunction.Index(modelRange, WorksheetFunction.Match(Cells(i, 8).Value, Range("F5:F12"), 0))
    Next i
End Sub

• Like example 1 and according to the video you will create a Command button and assign the macro named MultipleMatch_Value.

• Then after pressing the Search button, we will get the Model according to Order Id.

Sub MutipleMatch_Value()
    Dim i As Integer
    Dim modelRange As Range
    Set modelRange = Range("D5:D12")

D5:D12.
    For i = 5 To 7
    Cells(i, 9).Value = WorksheetFunction.Index(modelRange, WorksheetFunction.Match(Cells(i, 8).Value, Range("F5:F12"), 0))
    Next i
End Sub

3. Finding Matching Value in Column in Another Worksheet

In this section, we are going to find a matching value from another sheet. The above video demonstrates how we find a matching value from the “Dataset” worksheet and find the output in the “Output Data” worksheet. Below, we are going for a detailed demonstration.

• First, you have to create a worksheet and name it “Dataset” (you can give anything you like and change the code accordingly) and “Output Data”, another worksheet with the intended Order Id.
• Then we have to open a module and write the code below.

• You can copy the code from here.

Sub AnotherSheet_MatchValue()
    Dim modelRange As Range
    Set modelRange = Sheets("Dataset").Range("D5:D12")
    Sheets("Output Data").Range("C3").Value = WorksheetFunction.Index(modelRange, WorksheetFunction.Match(Sheets("Output Data").Range("B3").Value, Sheets("Dataset").Range("F5:F12"), 0))
End Sub

• We are going to add a search button and assign the macro with it by right-clicking on it, selecting Assign Macro from the context menu, and then selecting the proper macro.
• Finally, we get the output in the Output Data worksheet by clicking on the search button.

Sub AnotherSheet_MatchValue()
    Dim modelRange As Range
    Set modelRange = Sheets("Dataset").Range("D5:D12")

Sheets("Output Data").Range("C3").Value = WorksheetFunction.Index(modelRange, WorksheetFunction.Match(Sheets("Output Data").Range("B3").Value, Sheets("Dataset").Range("F5:F12"), 0))

4. Finding Matching Value in Column Using Find Function

• Like in example 1 we have prepared the range H4:I5 for the searching and created a button, named it Search. Now we give any Order Id from the left dataset.

• Now we have to create a module and write the following code below.

• You can copy the code from here.

Sub FindValueUsing_FIndFunction()
Dim orderID As Range
Set orderID = Range("F5:F12").Find(what:=Range("H5").Value, _
LookIn:=xlValues, LookAt:=xlWhole)
If Not orderID Is Nothing Then
Range("I5").Value = orderID.Offset(, -2).Value
Else
MsgBox "No Matched Data Found !!"
End If
End Sub

• Finally, you have to assign the Macro in the Search button like the assign Macro in example 1.

• Then pressing the Search button we will finally get the result.

Sub FindValueUsing_FIndFunction()

Dim orderID As Range

Set orderID = Range("F5:F12").Find(what:=Range("H5").Value, _
LookIn:=xlValues, LookAt:=xlWhole)

If Not orderID Is Nothing Then

Range("I5").Value = orderID.Offset(, -2).Value

Else
MsgBox "No Matched Data Found !!"
End If

5. Finding Matching Value from Table’s Column

• First, you will give the Dataset and press Ctrl+T to turn your dataset into a table and create a button naming it Search. Now we give any Order Id from the left dataset.

• Now we have to open a module and write the following code.

Sub OneMatch_Value()
    Dim modelRange As Range
    Set modelRange = Range("D5:D12")
    Range("I5").Value = WorksheetFunction.Index(modelRange, WorksheetFunction.Match(Range("H5").Value, Range("F5:F12"), 0))
End Sub

• We are assigning the macro in the Search Button like in the previous examples.
• After pressing the Search button, we get the final result.

Sub OneMatch_Value()
    Dim modelRange As Range
    Set modelRange = Range("D5:D12")

Range("I5").Value = WorksheetFunction.Index(modelRange, WorksheetFunction.Match(Range("H5").Value, Range("F5:F12"), 0))

6. Find String With Partial Match in VBA

In this example, we are going to find out values from a column only using a partial string.

• Like previous examples, we have created a button naming it as Search. Now we give any Partial MatchingString of Product ID from the left dataset.

• Now we will create a module and write the code below.

• You can copy the code from here.

Sub FindUsing_PartialMAtchingString()
Set myerange = Range("B5:F12")
Set ID = Range("h5")
Set Model = Range("I5")
Model.Value = Application.WorksheetFunction.VLookup _
("*" & Range("h5") & "*", myerange, 3, False)
End Sub

• Now you will assign the macro Search button.

• Assigning the macro in the Search button and pressing it, we finally get the result.

Set myerange = Range("B5:F12")

Set ID = Range("h5")

Set Model = Range("I5")

Model.Value = Application.WorksheetFunction.VLookup _
("*" & Range("h5") & "*", myerange, 3, False)

7. Finding First Matching Value in Column

In this section, we are going to find out the first matching value in the column. Here, Unit Price 1200$ found for both IPhone 13 and MI15 Pro but I want the first one.

• Similar to previous examples we have prepared the cells and created a button naming it Search. Now we give any Unit Price from the left dataset.

• We create a module, write the code below and assign the macro in the Search.

• You can copy the code from here.

Sub FirstMatch_Value()
    Dim modelRange As Range
    Set modelRange = Range("D5:D12")
    Range("I5").Value = WorksheetFunction.Index(modelRange, WorksheetFunction.Match(Range("H5").Value, Range("E5:E12").Value, 0))
End Sub

• Now assign the macro FirstMatch_Value in the search button.

• Finally, we get the result after pressing the Search.

    Dim modelRange As Range
    Set modelRange = Range("D5:D12")

    Range("I5").Value = WorksheetFunction.Index(modelRange, WorksheetFunction.Match(Range("H5").Value, Range("E5:E12").Value, 0))

End Sub

8. Finding Last Matching Value in Column

In this section, we are going to find out the last matching column value in VBA.

• Like previous examples, we have prepared the cells and created a button naming it as Search. Now we give any Unit Price from the left dataset.

• We created a module, write the code below and assign the macro in the Search.

• You can copy the code from here.

Sub LastMatch_Value()
    Dim ws As Worksheet
    Dim x As Variant
    Set ws = ThisWorkbook.Worksheets("LastMatch")
    For i = 12 To 5 Step -1
    If (Range("h5").Value = Cells(i, 5).Value) Then
           Range("I5").Value = Cells(i, 4).Value
    End If
    Exit For
    Next i
End Sub

• Then you will assign the macro LastMatch_Value in Search.

• Finally, we get the result after pressing the Search button.

 Dim ws As Worksheet
    Dim x As Variant
    Set ws = ThisWorkbook.Worksheets("LastMatch")

    For i = 12 To 5 Step -1

   If (Range("h5").Value = Cells(i, 5).Value) Then

Range("I5").Value = Cells(i, 4).Value

    End If
    Exit For

How to Find Range of Matching Value in Excel VBA

Here,  we are going to find out the Range of the match value. It helps us to figure out which range the Product Id exists.

• To do so, we have created a button naming it Search. Now we give any Unit Price from the left dataset.

• After opening a module you can write down the code below and assign the macro in the search range.

• You can copy the code from here.

Sub FindRange()
    Dim productID As String
    Dim dataRange As Range
    Dim matchRange As Range
    'Set the product ID and data range
    productID = Range("H5").Value
    Set dataRange = Range("B4:F12")
    'Search for the product ID in the data range
    Set matchRange = dataRange.Find(productID, LookIn:=xlValues, LookAt:=xlWhole)
    'Print the range of the matching cell in cell H5
    If Not matchRange Is Nothing Then
        Range("I5").Value = matchRange.Address
    Else
        Range("I5").Value = "Not found"
    End If
End Sub

• Now, we will assign the Macro  FindRange in Search Range button.

• Finally pressing search range, we can get the range of the Product Id.

Sub FindRange()
    Dim productID As String
    Dim dataRange As Range
    Dim matchRange As Range
    'Set the product ID and data range
    productID = Range("H5").Value
    Set dataRange = Range("B4:F12")
    'Search for the product ID in the data range

 Set matchRange = dataRange.Find(productID, LookIn:=xlValues, LookAt:=xlWhole)

    'Print the range of the matching cell in cell H5
    If Not matchRange Is Nothing Then
        Range("I5").Value = matchRange.Address
    Else
        Range("I5").Value = "Not found"
    End If

How to Find The Range of Blank Cell in a Column with Excel VBA

In this section, we are going to find out the range of the Blank Cell. It helps us to figure out which range the Blank Cell exists because you forgot to enter a value.

• To do so, we have to give names BlanK Cell  in H4 cells and create a button naming it as Search Blank Cell.

• After opening a module you can write down the code below and assign the macro in the Search Blank Cell.

• You can copy the code from here.

Sub FindBlankCell_Range()
    Dim blankCell As Range
    Dim dataRange As Range
    Set dataRange = Range("B4:F12")
    Set blankCell = dataRange.Find("", LookIn:=xlValues, LookAt:=xlWhole)
    If Not blankCell Is Nothing Then
        Range("H5").Value = blankCell.Address
    Else
        Range("H5").Value = "No blank cell found"
    End If
End Sub

• Now we will assign the FindBlankCell_Range in Search Blank Cell.

• So after assigning the macro FindBlankCell_Range in the Search Blank Cell button, we are going to press Finally, we get the range of blank cells.

    Dim blankCell As Range
    Dim dataRange As Range
    Set dataRange = Range("B4:F12")
    Set blankCell = dataRange.Find("", LookIn:=xlValues, LookAt:=xlWhole)

    If Not blankCell Is Nothing Then
        Range("H5").Value = blankCell.Address
    Else
        Range("H5").Value = "No blank cell found"
    End If

How to Find Column Range of a Specific Header in Excel VBA

In this section, we will find a specific column with a specific header. Only pressing Search Header will show the column.

• To do so, Like in example 1 first we have to give names  Specific Header and Column Range in H4 and I4 cells respectively, and create a button naming it as Search Header. Now we give any Column Header from the left dataset.

• Now we are going to write the code below and assign the macro in the Search Header.

• You can copy the code from here.

Sub FindHeaderRangeAndSelect()
    Dim header As String
    Dim dataRange As Range
    Dim headerRange As Range
    Dim columnRange As Range
    'Set the header and data range
    header = Range("H5").Value
    Set dataRange = Range("B4:F12") 
    'Search for the header in the data range
    Set headerRange = dataRange.Find(header, LookIn:=xlValues, LookAt:=xlWhole) 
    'If the header is found, get the range of the corresponding column
    If Not headerRange Is Nothing Then
        Set columnRange = dataRange.Columns(headerRange.Column - 1)
        Range("I5").Value = columnRange.Address
        columnRange.Select
    Else
        Range("I5").Value = "Header not found"
    End If
End Sub

• Now we are going to assign the Macro FindHeaderRangeAndSelect in the Search Header button.

• Now after assigning the macro FindHeaderRangeAndSelect in the Search Header button, we are going to press Search Header. Finally, we will get the final column of the specific header.

Sub FindHeaderRangeAndSelect()
    Dim header As String
    Dim dataRange As Range
    Dim headerRange As Range
    Dim columnRange As Range

    'Set the header and data range
    header = Range("H5").Value
    Set dataRange = Range("B4:F12")

    'Search for the header in the data range
    Set headerRange = dataRange.Find(header, LookIn:=xlValues, LookAt:=xlWhole)

    'If the header is found, get the range of the corresponding column
    If Not headerRange Is Nothing Then
        Set columnRange = dataRange.Columns(headerRange.Column - 1)
        Range("I5").Value = columnRange.Address
        columnRange.Select
    Else
        Range("I5").Value = "Header not found"
    End If

Things to Remember

• Use the “Find” method: VBA provides a built-in “Find” method that can be used to search for a specific value in a range or column.
• Define the search range: Before using the “Find” method, you need to define the range to search within. This can be done using the “Range” method.
• Use variables to store values: It’s a good practice to use variables to store the values you’re searching for, as well as the results of the search.
• Check for errors: When using the “Find” method, it’s important to check for errors, such as when the value is not found in the range.
• Use loops for multiple searches: If you need to search for multiple values in a column, you can use a loop to iterate through each value and perform the search.
• Consider performance: If you’re searching through a large range of cells, it’s important to consider performance and optimize your code to avoid slowing down the application.
• Use the “Offset” property to return adjacent values: Once you’ve found a matching value in a column, you can use the “Offset” property to return the value in an adjacent column or row.
• Use the “Cells” property to return the cell address: If you need to return the address of the cell containing the matching value, you can use the “Cells” property.
• Test your code: Always test your code thoroughly to ensure it’s working as expected before using it in a production environment.

Frequently Asked Questions (FAQs)

Q: What is VBA?

A: VBA stands for Visual Basic for Applications, and it is a programming language that is used to automate tasks in Microsoft Office applications, including Excel.

Q: How can I find a matching value in a column using VBA?

A: You can use the “Find” method in VBA to search for a specific value in a range or column. First, define the range to search within using the “Range” method, and then use the “Find” method to search for the value. You can also use a loop to search for multiple values in a column.

Q: What should I do if the value I’m searching for is not found in the column?

A: When using the “Find” method, it’s important to check for errors, such as when the value is not found in the range. You can use an “If” statement to check if the value was found, and then take appropriate action if it was not found.

Q: How can I return the value in an adjacent column or row when a matching value is found?

A: Once you’ve found a matching value in a column using the “Find” method, you can use the “Offset” property to return the value in an adjacent column or row. The “Offset” property allows you to specify the number of rows and columns to move from the current cell.

Q: How can I return the address of the cell containing the matching value?

A: If you need to return the address of the cell containing the matching value, you can use the “Cells” property. This property allows you to specify the row and column index of the cell you want to return.

Q: How can I optimize my VBA code for performance when searching through a large range of cells?

A: If you’re searching through a large range of cells, it’s important to optimize your code for performance. This can include using variables to store values and results, avoiding unnecessary loops, and using the “Application.ScreenUpdating” property to disable screen updating while the code is running.

Conclusion

In conclusion, the process of using Excel VBA to Find Matching Value in Column can greatly simplify and automate tasks in Excel. By utilizing the “Find” method and defining the search range, VBA can be used to efficiently search for values in large ranges of cells. With proper error handling, the “Offset” and “Cells” properties, and optimizing for performance, VBA can be a powerful tool for finding matching values in a column. With the knowledge of “find matching value in column in VBA”, users can enhance their Excel skills and save time on repetitive tasks.