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:
Range(“D1”).Value = WorksheetFunction.Match(Range(“B5”).Value, Range(“B2:B6”), 0)
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.
Table of contents
- VBA Application.Match function is used to locate the position of the specified value in the range of cells (Rows, Columns, and tables). It searches for the lookup value in the lookup array.
- The syntax is as follows:
- Arg1= is the Lookup value.
- Arg2= is the Lookup Array.
- Arg3= is the type of Match( -1, 0 or 1)
- Match types -1, 0, and 1 represent the exact or the next smallest match, the same match, and the exact or next largest value match, respectively.
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:
- Arg1= Lookup value. The value you need to find in the array.
- Arg2 = Lookup array. The range of arrays where you must find the desired lookup value.
- Arg3 = type of match. It includes the match type values -1, 0 or, 1.
- -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.
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.
Dim x As Integer
For x = 1 To 3
Cells(x, 4).Value = Application.Match(Cells(x, 3).Value, Range(“B2:B10”), 0)
- In the sub-procedure VBA_Match_Ex1(), the x variable is defined as an integer in the first line.
- Next, we use the For VBA loop for multiple lookup values.
- 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.
- 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.
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.
Range(“F2”).Value = Application.WorksheetFunction.VLookup(Range(“E2”).Value, Range(“A2:C7”), Application.WorksheetFunction.Match(Range(“F1”).Value, Range(“A1:C1”), 0), 0)
- Range(“F2”).Value – The cell where we want to see our result.
- 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.
- 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:
MatchRow = Application.Match(“test”, Columns(1), 0)
If IsError(MatchRow) Then
MsgBox “No Match”
MsgBox “Match row is” & MatchRow
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)
When we have the VBA Application.Match not working, it returns the #NA error. If the Match function is unable to locate the data even if it is present in the worksheet, the following are the reasons:
1. The cell/lookup value has unexpected characters or hidden spaces.
2. For example, the cell has text value and may be assigned as Numerical.
Solution: Unwanted characters or hidden spaces can be removed using CLEAN or TRIM functions.
The VBA Application.Match and Worksheetfunction.Match is almost similar and is used to find the match of a specified lookup value. However, it has a few differences, shown in the table below.
VBA Application.Match error 2042 occurs when the function cannot find a specified lookup value. The reasons may include:
We are looking at the wrong column, or the lookup value has unidentified characters.
Solution: Ensure the lookup value is searched in the correct worksheet or the range of cells.
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.
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 –
Leave a Reply