Same as we have Index and Match in the worksheet as lookup functions we can also use Match functions in VBA as a lookup function, this function is a worksheet function and it is accessed by the application. worksheet method and since it is a worksheet function the arguments for Match function are similar to the worksheet function.
VBA Match Function
VBA Match function looks for the position or row number of the lookup value in the table array i.e. in the main excel table.
In a worksheet, lookup functions are an integral part of the excel. Some of the important lookup functions are VLOOKUP, HLOOKUP, INDEX, and MATCH. Unfortunately, we don’t have these functions as VBA functions. However, we can use them as worksheet functions in VBA.
In this article, I will show you how to use one of the worksheet lookup function MATCH in VBA as a worksheet function.
How to Use MATCH Function in VBA Excel?
We will show you a simple example of using the Excel MATCH function in VBA.
In VBA, we can use this MATCH formula in excel as worksheet function. Follow the below steps to use the MATCH function in VBA.
Step 1: Create a subprocedure by giving a macro name.
Step 2: In E2 cell we need the result, so start the code as Range (“E2”).Value =
Sub Match_Example1() Range("E2").Value = End Sub
Step 3: In E2 cell value should be the result of the MATCH formula. So in order to access the VBA MATCH function, we need to use the property “WorksheetFunction” first. In this property, we will get all the available worksheet function list.
Step 4: Select MATCH function here.
Sub Match_Example1() Range("E2").Value = WorksheetFunction.Match( End Sub
Step 5: Now the problem starts because we don’t get exact syntax name rather we get syntax as “Arg1, Arg2, Arg3” like this. So you need to be absolutely sure of syntaxes here.
Our first argument is LOOKUP VALUE, our LOOKUP VALUE is in the cell D2, so select the cell as Range (“D2”).Value.
Sub Match_Example1() Range("E2").Value = WorksheetFunction.Match(Range("D2").Value, End Sub
Step 6: The Second argument is Table Array, our table array range is from A2 to A10. So select the range as “Range (“A2: A10”)”
Sub Match_Example1() Range("E2").Value=WorksheetFunction.Match(Range("D2").Value,Range("A2:A10"), End Sub
Step 7: Now the final argument is MATCH TYPE. We need an exact match, so enter the argument value as zero.
Sub Match_Example1() Range("E2").Value = WorksheetFunction.Match(Range("D2").Value, Range("A2:A10"), 0) End Sub
Run the macro we will get the position of whatever the year name is there in the cell D2.
Example #2 – VBA Match From Another Sheet
Assume the same set of data from the above is there on two different sheets. For example, table array is there in the sheet name called “Data Sheet” and Lookup Value is there in the sheet name called “Result Sheet”.
In this case, we need to refer worksheets by its name before we refer to the ranges. Below is the set of codes with sheet names.
Sub Match_Example2() Sheets("Result Sheet").Range("E2").Value = WorksheetFunction.Match(Sheets("Result Sheet").Range("D2").Value, Sheets("Data Sheet").Range("A2:A10"), 0) End Sub
Example #3 – VBA Match Function with Loops
If the result we want in a single cell then no problem, but if the result has to come in more than one cell then we need to use a VBA loop to get the result in all the cells.
Assume you have a data like this.
In these cases it is a herculean task to write lengthy codes, so we switch to loops. Below is the set of code which will do the job for us.
Sub Match_Example3() Dim k As Integer For k = 2 To 10 Cells(k, 5).Value = WorksheetFunction.Match(Cells(k, 4).Value, Range("A2:A10"), 0) Next k End Sub
This set of codes will get the result in just the blink of an eye.
This has been a guide to Excel VBA Match Function. Here we learned how to use Match Function in VBA along with some simple to advanced examples. Below are some useful excel articles related to VBA –