VBA Application.Match

Excel VBA Application.Match

One of the best things about VBA is it has its own function and also it will allow us to access all the worksheet functions under the “Worksheet Function” class. You must have already used MATCH function as a worksheet function, but it is not a VBA function, so we need to access under worksheet function class. In this article, we will show you how to use the MATCH function in VBA using the Application method.

Quick Recap of MATCH Function

MATCH is a lookup function that looks for the position of the lookup value in the mentioned lookup array. For example, look at the below image of the data.

In the above data, we have months from A2 to A6, and if we want to know in which place “Mar” month occurs, then we can use the MATCH function.

Below is the syntax of the MATCH function.

MATCH (Lookup Value, Lookup Array, [Match Type])

• Lookup Value: For which value we are looking for the position in the lookup array.
• Lookup Array: In which array we are looking for the position of the lookup value.
• [Match Type]: For this, we can provide three arguments.

1. 1 = Less Than
2. 0 = Exact Match
3. -1 = Greater Than

Most of the time, we use only “0 Exact Match”.

How to Use Application.Match Function in VBA?

Example #1

Look at the below data in excel.

From the above data, we need to find the position of the “Mar” month in the range of cells from A2 to A6. Since we need results in D2 cells straight away start the code as Range(“D2”).Value =.

To arrive value in D2 cell, we need to use MATCH worksheet function, so to access this first, we need to access the APPLICATION object and then WORKSHEET FUNCTION object.

Enter dot to see a list of worksheet functions.

Choose “Match” from the list.

One of the problems in VBA while using worksheet functions is we do not see exact syntax as we see with worksheet function. This is one of the reasons we have explained the syntax at the beginning only.

So, the first argument is lookup value, i.e. for which value we are looking to find the place. In this case, we are looking to find the place for “Mar” which is in the C2 cell, so supply the cell reference.

The next argument is lookup array, i.e. in which range we are looking for the position of a lookup value, for this supply cells from A2 to A6.

The last argument will be an exact match, so supply 0.

Code:

Sub Match_Example1()
Range("D2").Value = Application.WorksheetFunction.Match(Range("C2").Value, Range("A2:A6"), 0)
End Sub

Ok, we are done with the formula.

Run the code through the F5 key and see what we get.

So we have got the result as 3 because the value “Mar” is in the place of 3^rd position in the range A2 to A6.

This MATCH function can provide the position of the lookup value. However, MATCH function largely used with the VLOOKUP function to supply the column index number based on the column header.

Example #2

Now we will see how to use MATCH as a supporting function for the VLOOKUP function.

Look at the below data.

In the above table, we are looking at the year 2018 “Feb” month sales, so we need to use the VLOOKUP function. VLOOKUP is also a worksheet function, so access this like how we have accessed the MATCH function.

Lookup Value will be G2 cell, so supply cell address.

Code:

Sub Match_Example2()
Range("H2").Value = Application.WorksheetFunction.VLookup(Range("G2").Value,
End Sub

Table Array will be from A2 to D6.

Code:

Sub Match_Example2()
Range("H2").Value = Application.WorksheetFunction.VLookup(Range("G2").Value,Range("A2:D6"),
End Sub

Now we need to provide from which column of the table array we are looking for the result, so this will be from the 3^rd column. Instead of supplying the column number as 3, let’s use the MATCH function.

Code:

Sub Match_Example2()
Range("H2").Value = Application.WorksheetFunction.VLookup(Range("G2").Value, Range("A2:D6"), Application.WorksheetFunction.Match(Range("H1").Value, Range("A1:D1"), 0), 0)
End Sub

So MATCH function provides the column number from the range A1 to D1 for the month “Feb”. Let’s run the code and see how it gets column numbers automatically.

There you go we have a result from column number 2 thanks to automatic column number supply from MATCH function.

Things to Remember

• MATCH function looks for the position of the lookup value in the selected array table.
• The MATCH function is mainly used with the VLOOKUP function to supply the column index number automatically using column heading.
• The MATCH function is available as a worksheet function in VBA.

Recommended Articles

This has been a guide to VBA Application.Match. Here we discuss how to use the MATCH function in VBA using the Application method along with practical examples and downloadable excel template. you may learn more from the following articles –

• VBA LOOKUP
• VLookup in VBA Excel
• VBA XLUP
• VBA Solver