EDUCBA

Home VBA VBA Resources VBA Lookup & Reference Functions VBA Match

VBA Match

Madhuri Thakur
Article byMadhuri Thakur

Updated June 8, 2023

VBA Match Function

 

 

Excel 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. For example, VLOOKUP, HLOOKUP, MATCH, INDEX, etc. These are the lookup functions that are more important than others. Regretfully, we don’t have the same functions available in VBA for making things easier. However, we can use these functions as worksheet functions under the VBA script to make our lives easier.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Today, we are about to learn the MATCH function which can be used as a worksheet function under VBA.

VBA Match has the same use as the Match formula in Excel. This function in MS Excel VBA finds a match within an array with reference to the lookup value and prints its position. This function becomes useful when you need to evaluate the data based on certain values. For example, VBA MATCH is helpful if you have the salary data of employees and you need to find out the numeric position of an employee in your data who has salary less than/greater than/equals to a certain value. It is really helpful in analyzing the data and also one line of code can automate the things for you.

Syntax of Match Function in Excel VBA

VBA Match has the following syntax:

Formula

Where,

  • Arg1 – Lookup_value – The value you need to lookup in a given array.
  • Arg2 – Lookup_array – an array of rows and columns which contain possible Lookup_value.
  • Arg3 – Match_type – The match type which takes value -1, 0 or 1.

If match_type = -1 means that the MATCH function will find out the smallest value which is greater than or equals to the lookup_value. For this to happen, the lookup_array must be sorted in descending order.

If match_type = 0 means that the MATCH function will find out the value which is exactly the same as that of the lookup_value.

If match_type = +1 it means that the MATCH function will find out the largest value which is lesser than or equals to the lookup_value. For this to happen, the lookup_array must be sorted in ascending order. The default value for the match type is +1.

How to Use Excel VBA Match Function?

We will learn how to use a VBA Match Excel function with few examples.

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

VBA Match Function – Example #1

Suppose we have data as shown below:

Example 1-1

We need to find who from this list have salary € 30,000 along with position in Excel.

Though in this data set we can manually configure this, please think on a broader picture, what if you have millions of rows and columns?

Follow the below steps to use MATCH function in VBA.

Step 1: Define a sub-procedure by giving a name to macro.

Code:

Sub exmatch1()

End Sub

VBA Match Example 1-2

Step 2: Now, we want our output to be stored in cell E2. Therefore, start writing the code as Range(“E2”).Value =

This defines the output range for our result.

Code:

Sub exmatch1()

  Range("E2").Value =

End Sub

VBA Match Example 1-3

Step 3: Use WorksheetFunction to be able to use VBA functions.

Code:

Sub exmatch1()

  Range("E2").Value = WorksheetFunction

End Sub

VBA Match Example 1-4

Step 4: WorksheetFunction has a variety of functions that can be accessed and used under VBA. After “WorksheetFunction”, put a dot (.) and then you’ll be able to access the functions. Choose the MATCH function from the dropdown list.

Code:

Sub exmatch1()

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

End Sub

VBA Match Example 1-5

Step 5: Now, give the arguments to the MATCH function. Like Lookup_value. Our Lookup_value is stored in cell D2 as shown in the below screenshot. You can access it under the MATCH function using Range function.

Code:

Sub exmatch1()

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

End Sub

VBA Match Example 1-7

Step 6: The Second argument is Lookup_array. This is the table range within which you want to find out the position of Lookup_value. In our case, it is (B1:B11). Provide this array using the Range function.

Code:

Sub exmatch1()

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

End Sub

VBA Match Example 1-8

Step 7: The Last argument for this code to work out is Match_type. We wanted to have an exact match for Lookup_value in given range> Therefore, give Zero (0) as a matching argument.

Code:

Sub exmatch1()

  Range("E2").Value = WorksheetFunction.Match(Range("D2").Value, Range("B1:B11"), 0)

End Sub

VBA Match Example 1-9

Step 8: Run this code by hitting F5 or Run button and see the output.

Result of Example 1-10

You can see in Cell E2, there is a numeric value (6) which shows the position of the value from cell D2 through range B1:B11.

Example #2 – VBA Match Function with Loops

It is easy when you have only one value to lookup for in the entire range. But, what if you need to check the position for a number of cells? It would be harsh on a person who is adding to ask him to write the separate codes for each cell.

Example 2-1

In such cases, MATCH function can be used with loop (especially For loop in our case). See the following steps to get an idea of how we use MATCH function with loop.

Step 1: Define a subprocedure by giving a name to macro.

Code:

Sub Example2()

End Sub

VBA Match Example 2-2

Step 2: Define an integer that can hold the value for multiple cells in the loop.

Code:

Sub Example2()

Dim i As Integer

End Sub

VBA Match Example 2-3

Step 3: Use For loop on the integer to use the different lookup values whose position can be stored in column E.

Code:

Sub Example2()

Dim i As Integer

For i = 2 To 6

End Sub

VBA Match Example 2-4

Step 4: Now, use the same method we used in example 1, just instead of Range, we will use the Cells function and will be using a two-dimensional array (Rows and columns) in contrast to the first example.

Code:

Sub Example2()

Dim i As Integer

For i = 2 To 6
Cells(i, 5).Value = WorksheetFunction.Match(Cells(i, 4).Value, Range("B2:B11"), 0)
Next i

End Sub

VBA Match Example 2-5

Here, Cells(i, 5).Value = stores the value of resulting positions in each row from 2 to 6 (row i) in column E (column number 5). Under Match function, Cells(i, 4).Values checks for each Lookup_value present in row 2 to 6 in the 4th column. This lookup value then searched in Array B2:B11 in excel sheet where data is present and relative positions can be stored in each row of column 5 (column E).

Step 5: Run this code by hitting F5 or Run button simultaneously and see the result. It will almost pull out the magic in a piece of code with a single line.

Result of Example 2-6

In this article, we learned how we can use MATCH function under VBA as a special case of WorksheetFunction.

Things to Remember

  • Lookup_value can be number/text/logical value or can be a cell reference to a number, text or logical value.
  • By default, Match_type can be considered as 1, if omitted/not mentioned.
  • As similar to Excel MATCH function, VBA MATCH also gives the relative position of a Lookup_value under Lookup_array and not the value itself.
  • If a match is not found, a relative excel cell will be filled with #N/A.
  • If MATCH function is used on Text values, it is not able to differentiate between lowercases and uppercases. For Example, Lalit and lalit are same. So do LALIT and lalit.
  • Wildcard characters can be used if you are finding out the exact match (i.e. match type is zero). Wildcard character asterisk (*) can be used to find out a series of characters. While a question mark (?) can be used to find out a single character.

Recommended Articles

This is a guide to VBA Match Function. Here we discuss VBA Match and how to use Excel VBA Match Function along with practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. VBA On Error
  2. VBA Number Format
  3. VBA VLOOKUP
  4. VBA Function