VBA Match

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

vba match formula

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 the excel. Some of the 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 don’t 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, 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.

You can download this VBA Match Excel Template here – VBA Match Excel Template

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 subprocedure by giving a macro name.

Code:

Sub Match_Example1()
vba match example 1.1

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

Code:

Sub Match_Example1()
Range("E2").Value =
End Sub
Range.value 1.2

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.

visual basic match example 1.3

Step 4: Select the MATCH function here.

Code:

Sub Match_Example1()
Range("E2").Value = WorksheetFunction.Match(
End Sub
vba match example 1.4

Step 5: Now, the problem starts because we don’t get the 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.

Code:

Sub Match_Example1()

Range("E2").Value = WorksheetFunction.Match(Range("D2").Value,

End Sub
vba match example 1.5

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
visual basic application 1.6

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
visual basic application 1.7

Run the macro we will get the position of whatever the year name is there in the cell D2.

vba match example 1.8

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.

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
vba match example 2.1

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 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 a data like this.

visual basic application 3.1

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.

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
vba match example 3.3

This set of codes will get the result in just the blink of an eye.

vba match example 3.3

Recommended Articles

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 –

  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>