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.
- 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.
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?
Sub example1_match(): This means we define a subprocedure by giving the macro name.
Range("G5").Value: We want the output to be stored in cell G5.
WorksheetFunction: By using this code we will be able to access the VBA functions.
Match(Range("F5").Value, Range("D5:D10"), 0): Here, we use the Match function in VBA. As we want to take the value from cell F5 and find out the position in range D5:D10.
End Sub: This means we end the procedure.
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.
- 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.
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.
- 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.
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?
For i = 5 To 8: This means we want the loop runs starting with row 5 and ending with row 8.
Cells(i, 7).Value: This saves the value of the resultant locations in each row from 5 to 8 rows in Column G which is column number 7.
Match(Cells(i, 6).Value, Range("D5:D10"), 0): Cells can be matched using the Match function (i, 6). Values search for each Lookup value found in rows 5 through 8 of the 6th column. Then searched in array D5:D10 on an Excel sheet where data is available.
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.
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.