Vba Application Match Step By Step Guide With Examples

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.

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

Quick Recap of MATCH Function

The MATCH function 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. If we want to know where “Mar” month occurs, 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 = Less Than0 = Exact Match-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.

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

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

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.

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

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

Table Array will be from A2 to D6.

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.

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.

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.

Excel VBA Application.Match#

Quick Recap of MATCH Function#

How to Use Application.Match Function in VBA?#

Example #1#

Example #2#

Things to Remember#

Recommended Articles#