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.

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA Application.Match (wallstreetmojo.com)

Quick Recap of MATCH Function

MATCH is a lookup functionLookup FunctionThe 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 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 functionMATCH FunctionThe 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.

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 FUNCTIONWORKSHEET FUNCTIONThe worksheet function in VBA is used when we need to refer to a specific worksheet. When we create a module, the code runs in the currently active sheet of the workbook, but we can use the worksheet function to run the code in a particular worksheet.read more 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 referenceCell ReferenceCell reference in excel is referring the other cells to a cell to use its values or properties. For instance, if we have data in cell A2 and want to use that in cell A1, use =A2 in cell A1, and this will copy the A2 value in A1.read more.

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 functionVLOOKUP FunctionThe 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 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