VBA Match

We have Index and Match in the worksheet as lookup functions. In addition, we can also use Match functions in VBA as a lookup function. This function is a worksheet function and the Application.Worksheet method. Since it is a worksheet function, the arguments for the 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 tableExcel TableIn excel, tables are a range with data in rows and columns, and they expand when new data is inserted in the range in any new row or column in the table. To use a table, click on the table and select the data range.read more.

In a worksheet, lookup functionsLookup FunctionsThe LOOKUP excel function searches a value in a range (single row or single column) and returns a corresponding match from the same position of another range (single row or single column). The corresponding match is a piece of information associated with the value being searched. read more are an integral part of Excel. Some important lookup functions are VLOOKUPVLOOKUPThe VLOOKUP excel function searches for a particular value and returns a corresponding match based on a unique identifier. A unique identifier is uniquely associated with all the records of the database. For instance, employee ID, student roll number, customer contact number, seller email address, etc., are unique identifiers. read more, HLOOKUP, INDEX, and MATCHMATCHThe MATCH function looks for a specific value and returns its relative position in a given range of cells. The output is the first position found for the given value. Being a lookup and reference function, it works for both an exact and approximate match. For example, if the range A11:A15 consists of the numbers 2, 9, 8, 14, 32, the formula ‘MATCH(8,A11:A15,0)’ returns 3. This is because the number 8 is at the third position. read more. Unfortunately, we do not have these functions as VBA functionsVBA FunctionsVBA functions serve the primary purpose to carry out specific calculations and to return a value. Therefore, in VBA, we use syntax to specify the parameters and data type while defining the function. Such functions are called user-defined functions.read more. However, we can use them as worksheet functions in VBA.

In this article, we will show you how to use one of the worksheet lookup functions, 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.

Example #1

In VBA, we can use this MATCH formula in excelMATCH Formula In ExcelThe MATCH function looks for a specific value and returns its relative position in a given range of cells. The output is the first position found for the given value. Being a lookup and reference function, it works for both an exact and approximate match. For example, if the range A11:A15 consists of the numbers 2, 9, 8, 14, 32, the formula ‘MATCH(8,A11:A15,0)’ returns 3. This is because the number 8 is at the third position. read more as a worksheet function. Follow the below steps to use the MATCH function in VBA.

Step 1: Create a sub procedure by giving a Macro name.

Code:

Sub Match_Example1()

Step 2: In the E2 cell, we need the result, so start the code as Range (‘E2’).Value =

Code:

Sub Match_Example1() Range(“E2”).Value = End Sub

Step 3: In E2, the cell value should result from the MATCH formula. So, 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 lists.

Step 4: Select the MATCH function here.

Code:

Sub Match_Example1() Range(“E2”).Value = WorksheetFunction.Match( End Sub

Step 5: The problem starts because we do not get the exact syntax name. Rather, we get syntax like ‘Arg1, Arg2, Arg3’. So, it would help if you were sure of the syntaxes here.

Our first argument is LOOKUP VALUE. Our LOOKUP VALUE is in cell D2, so select the cell as Range (‘D2’).Value.

Code:

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′).’

Code:

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.

Code:

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 cell D2.

Example #2 – VBA Match From Another Sheet

Assume the same data set from the above is on two different sheets. For example, the table array is in the sheet name called ‘Data Sheet,’ and Lookup Value is in the sheet name called ‘Result Sheet.’

In this case, we need to refer to worksheets by their name before we refer to the ranges. Below is the set of codes with sheet names.

Code:

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 is 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 loopVBA LoopA VBA loop in excel is an instruction to run a code or repeat an action multiple times.read more to get the result in all the cells.

Assume you have data like this.

Writing lengthy codes is arduous in these cases, so we switch to loops. Below is the set of codes that will do the job for us.

Code:

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