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.


Download Practice Workbook

You can download the workbook and practice with them.


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. Because it is a worksheet function, the parameters for the Match function are similar to those for the worksheet function.


3 Examples of Excel VBA to Match Value in Range

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.

Match Value in Range with VBA Match Function in Excel

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.

Match Value in Range with VBA Match Function in Excel

  • 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 Rub Sub button.

Match Value in Range with VBA Match Function in Excel

  • 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 (5 Examples)


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.

Use Excel VBA to Match Value from Another Worksheet

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.

Use Excel VBA to Match Value from Another Worksheet

Read More: How to Match Data in Excel from 2 Worksheets


Similar Readings


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?

  • 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.

Read More: Excel Find Matching Values in Two Columns


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.

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. Or you can have a glance at our other articles in the ExcelDemy.com blog!


Related Articles

Tags:

Sabrina Ayon

Sabrina Ayon

Hi there! This is Sabrina Ayon. I'm really excited to welcome you to my profile. Currently, I'm working in SOFTEKO as a Excel and VBA Content Developer. I'm a student of United International University and my program is Computer Science and Engineering. I love working with computers and solving problems. I’ve always been interested in research and development. So, here I will be posting articles related to Microsoft Excel. Hoped this may help you. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo