VBA Application.Match

What is Excel VBA Application.Match?

VBA Application.Match is a lookup function that locates the position of the item mentioned in the lookup array in rows or columns, or tables.

Suppose we have some data in an Excel sheet column with the names Olive, Henry, Edward, Isabelle, and Sophia. The names are in cells A2-A6, and their marks are in cells B2-B6, respectively. Suppose we wish to find the position of Isabelle’s marks within the range B2-B6, the code for VBA Application.Match is shown below:

Sub VBA_Match_Ex()
Range(“D1”).Value = WorksheetFunction.Match(Range(“B5”).Value, Range(“B2:B6”), 0)
End Sub

Run the VBA code, and you will see position 4 in cell D1, suggesting the exact position of Isabelle’s marks. Here, we get 4 and not 5 since our cell range starts at B2.

Quick Recap of MATCH Function

In Excel, the MATCH function is already available, and it allows users to find the location of the desired item (Lookup) in a lookup array. Then, it returns the location of the search item to the user.

Syntax of the Match function in VBA:

MATCH(Arg1,Arg2,[Arg3])

1. Arg1= Lookup value. The value you need to find in the array.
2. Arg2 = Lookup array. The range of arrays where you must find the desired lookup value.
3. Arg3 = type of match. It includes the match type values -1, 0 or, 1.

Here,

• -1 signifies the exact or the next smallest match.
• 0 represents the exact match
• 1 gives you the exact or next largest value match.

Syntax of Match function in Excel:

MATCH has a similar syntax in Excel too with the same type of arguments as in VBA Match.

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

How to Use Application.Match Function in VBA?

Let us learn the use of Application.Match function in VBA with a few examples.

Example #1

VBA Application.Match function using the For loop

Consider the example used in the introduction. We have added the marks of some more students to it. We have their total marks in Semester I in Column B, and we must find the location of the marks of three students named Isabelle, Jenner, and William. Here, the Application.Match function will serve our purpose. Just follow the below steps:

Step 1: The data is present in the Excel sheet as follows. Column C shows the lookup values(total marks) of the three students whose positions we are looking for.

Step 2: Write the below code in the VBA Editor.

Code:

Sub VBA_Match_Ex1()
Dim x As Integer
For x = 1 To 3
Cells(x, 4).Value = Application.Match(Cells(x, 3).Value, Range(“B2:B10”), 0)
Next x
End Sub

Explanation:

1. In the sub-procedure VBA_Match_Ex1(), the x variable is defined as an integer in the first line.
2. Next, we use the For VBA loop for multiple lookup values.
3. Cells(x, 4).Value ensures that the result will be printed in the fourth column for the corresponding values in the third column. Using the Match function, it will check the lookup values in the third column.
4. Finally, after searching for them within the B2:B10 cell range, it matches these lookup values and prints their corresponding positions.

Step 3: Run the code by clicking the Run button in the VBA ribbon or with F5. You can see the exact positions of the values you were looking for.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Example #2

In this example, we will see the use of the Match function with the VLOOKUP function. Here we have taken some fictitious data of shipments of different shopping websites for the purpose of understanding this function.

Step 1: Write the required data in an Excel worksheet. Here, we must find the shipments of the shopping website, Target. Therefore, we will use the VLOOKUP function which is very similar to the Match function.

The value here is in the E2 cell.

Step 2: Write the below code in the VBA Editor.

Code:

Sub Match_Ex2()
Range(“F2”).Value = Application.WorksheetFunction.VLookup(Range(“E2”).Value, Range(“A2:C7”), Application.WorksheetFunction.Match(Range(“F1”).Value, Range(“A1:C1”), 0), 0)
End Sub

Explanation:

1. Range(“F2”).Value – The cell where we want to see our result.
2. Application.WorksheetFunction.VLookup(Range(“E2”).Value – Here, the VLOOKUP function is assigned to find the value corresponding to cell E2 within A2 to C7 cells.
3. Application.WorksheetFunction.Match(Range(“F1”).Value, Range(“A1:C1”), 0), 0)– The Match function is assigned here to find the corresponding value of the heading specified in cell F1(Day2 Shipments) within the A1 to C7 cells.

Step 3: Run the code, and you will see the value of Day 2 shipments of Target in cell F2.

Error in VBA Application.Match

 Suppose we have a blank Excel sheet. To understand error handling in Application.Match, look at the code given below:

Sub MismatchErrorHandling()
MatchRow = Application.Match(“test”, Columns(1), 0)
If IsError(MatchRow) Then
MsgBox “No Match”
Else
MsgBox “Match row is” & MatchRow
End If
End Sub

Here we try to find if a value “test” exists in Column 1 and its corresponding position. If the value does not exist, we use the IF loop to handle the error.

• Run the code, and you will get the message “No match” in the message box as the Excel sheet is blank.

Important Things To Note

• When the Application.Match function is used to search a text value using the VLOOKUP function.
• The VLOOKUP worksheet functions can be combined with the INDEX function to create a lookup formula.
• We can find the position of lookup values such as numbers, text, or logical values within the lookup array.
• The MATCH function will not be able to differentiate between the lower and upper case. For Example, MATCH or match is considered the same.
• VBA Application. Match recognizes an error and will be able to point it out. It attempts to create a match of the lookup value. If it does not find it, it creates An Error Object.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the VBA Application.Match, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to VBA Application.Match. Here we explain how to use Application.match function in Excel VBA with examples and downloadable template. You may learn more from the following articles –

• VBA Dictionary
• VBA Selection Range
• VBA Find