Excel VBA to Match Value in Range (3 Examples)

While working with Microsoft Excel, sometimes we need to find the match value in a range. We can easily do this with Excel functions. There are functions in VBA that we may use, but none of them are equal to MATCH. In this article, we will learn how to match a value in a range with Excel VBA.

Introduction to Excel VBA Match Function

The MATCH function in Excel VBA is a helpful constructed lookup function that, like VLOOKUP, HLOOKUP, and the INDEX functions, returns the location of identical or comparable matching of the lookup values obtained in arrays or a database. This function is a worksheet function that the program uses. Since it is a worksheet function, the parameters for the Match function are similar to those for the worksheet function.

Excel VBA to Match Value in Range: 3 Examples

1. Match Value in Range with VBA Match Function in Excel

To use the Excel VBA Match function, for finding the match value in a range, we are going to use the following dataset. The dataset has some students’ names in Column C, their marks on a specific subject in Column D, and the serial number of each student in Column B. Now, suppose we want to find the match position in cell G5 of a specific mark, and the mark which we want to match is in cell F5.
Let’s demonstrate the procedure to find match values in a range using the excel VBA Match function.

STEPS:

• First, go to the Developer tab from the ribbon.
• Second, click on Visual Basic to open the Visual Basic Editor, where we write the code. Or, press Alt + 11 to open the Visual Basic Editor.

• Another way to open Visual Basic Editor is just to right-click on the worksheet and click on View Code.

• This will open the Visual Basic Editor. Now, write the code there.

VBA Code:

Sub example1_match()
Range("G5").Value = WorksheetFunction.Match(Range("F5").Value, Range("D5:D10"), 0)
End Sub

• After that, to run the code, press the F5 key on your keyboard or click on the Run sub button.

• Finally, you can see that the match is found in position 5.

🔎 How Does the VBA Code Work?

Read More: Excel VBA to Match String in Column

2. Use Excel VBA to Match Value from Another Worksheet

We can find match values in the range from another worksheet, using the VBA Match function in Excel. Assume that, we have a dataset in a sheet named Data and we want the result in the sheet name Result. And we are using the same dataset. Now, let’s follow the steps to do this.

STEPS:

• In the beginning, by the same token as the earlier example, go to the Developer tab on the ribbon.
• Then, click on Visual Basic or press Alt + F11 to open the Visual Basic Editor.
• Instead of this, to open the Visual Basic Editor, simply right-click on the sheet and select View Code.

• Now, write down the VBA code.

VBA Code:

Sub example2_match()
Sheets("Result").Range("C5").Value = WorksheetFunction.Match(Sheets("Result").Range("C5").Value, Sheets("Data").Range("D5:D10"), 0)
End Sub

• Next, run the code by pressing the F5 key or clicking the Run Sub button.

• And, the result is found in the Result sheet.

3. Excel VBA Loops to Get Matched Value in Range

Suppose, we want multiple marks to match the value, we will use the VBA loops for this. We are using the same dataset as before. Now, we want the match position in Column G, and the marks that we want to find the match are in Column F. Let’s see the steps down.

STEPS:

• Firstly, from the ribbon, go to the Developer tab.
• Secondly, to open the Visual Basic Editor,  click on Visual Basic or press Alt + F11.
• Or, simply right-click on the sheet and select View Code.

• This will open the Visual Basic Editor.
• Now, type the code there.

VBA Code:

Sub example3_match()
Dim i As Integer
For i = 5 To 8
Cells(i, 7).Value = WorksheetFunction.Match(Cells(i, 6).Value, Range("D5:D10"), 0)
Next i
End Sub

• After that, pressing the F5 key or clicking the Run Sub button will run the code.

• And, you will be able to see the result in Column G.

🔎 How Does the VBA Code Work?

Things to Keep in Mind

• If the match type is missing or not specified, it is assumed to be 1.
• If no match is detected, a related Excel field will be blank.
• Lookup value might be a numeric, character, or logical data, or a cell reference to a quantity, text, or logical significance.

Download Practice Workbook

You can download the workbook and practice with them.

Conclusion

The above methods will assist you to match values in a range in Excel VBA. Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section.