VBA Application.Match

Last Updated :

-

Blog Author :

Jeevan A Y

Edited by :

Ashish Kumar Srivastav

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

arrow

Excel VBA Application.Match

One of the best things about VBA is that it has its function. Also, it will allow us to access all the worksheet functions under the “Worksheet Function” class. You must have already used the MATCH function as a worksheet function, but it is not a VBA function. So, we need to access it under the worksheet function class. This article will show you how to use the application method, the MATCH function in VBA.

VBA Application.Match
Table of contents

Quick Recap of MATCH Function

The MATCH function 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. If we want to know where “Mar” month occurs, we can use the MATCH function.

Below is the syntax of the MATCH function.

MATCH (Lookup Value, Lookup Array, )

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

VBA Application match Example 1

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, start the code as Range(“D2”).Value =.

VBA Application match Example 1-1

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

VBA Application match Example 1-2

Enter dot to see a list of worksheet functions.

VBA Application match Example 1-3

Choose “Match” from the list.

VBA Application match Example 1-4

One of the problems in VBA while using worksheet functions is we do not see exact syntax as we see with worksheet functions. It 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.

Example 1-5

The next argument is the 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.

Example 1-6

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

We have finished with the formula.

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

VBA Application match Example 1-7

So, we got the result as 3 because the value “Mar” is in the third position in the range A2 to A6.

This MATCH function can provide the position of the lookup value. However, the MATCH function is 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.

Data Example 2

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.

Example 2-1

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 the result from which column of the table array we are looking for. So, this will be from the third column. Instead of supplying the column number as 3, let us 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, the MATCH function provides the column number from the range A1 to D1 for the month “Feb.” Let us run the code and see how it gets column numbers automatically.

VBA Application match Example 2-2

We have column number 2, thanks to the MATCH function's automatic column number supply.

Things to Remember

  • The 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 using the column heading automatically.
  • The MATCH function is available as a worksheet function in VBA.