# VBA Match Article byJeevan A Y 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 .

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.

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

#### Example #1

In VBA, we can use this MATCH formula in excel 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()`

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

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 the MATCH function here.

Code:

```Sub Match_Example1()
Range("E2").Value = WorksheetFunction.Match(
End Sub```

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

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

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

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

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

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