VBA Match

What is VBA Match Function?

VBA MATCH is a worksheet function that can give us the position of the lookup value given in a column or row. VBA MATCH is a lookup function but not a VBA function. Hence, it can be used only under the worksheet function category. We don’t have any lookup functions available in VBA.

If you have already used the MATCH excel function, all the arguments, and working methodologies are the same. It takes a lookup value, looks for it, and returns its position in the given range. The return value is always numerical. If you have already used the MATCH function in the Excel worksheet, all the arguments and working methodologies are the same. For example, look at the following data.

Vba Match Intro

We have data in range A1 to B9 and from this range we need to find the position of the “Emp ID” 21194 in cell E2. We can use the following VBA MATCH function to code get the position of the given emp id.

Vba Match Intro - Code

Once we execute this code, we will get the position of the given emp id in cell E2.

Vba Match Intro - Output

In the VBA Match value in the range A2 to A9, for the emp id “21194,” and returns the position as 5.

Vba Match Intro - Output-position

As VBA MATCH is a function, we must understand the syntax of the function.

MATCH(Arg1, Arg2, Arg3)

In VBA we don’t get clear clarity on the arguments. However, let us explain in this detail to make you understand this better.

  • Lookup Value (Arg1): Lookup value is the value for which we are trying to get the position from the given range in the second argument.
  • Lookup Range (Arg2): In the given range, we will look to find the position for the lookup value given in the first argument.
  • Match Type (Arg3): We must provide the matching type in this argument. We can provide the following three values based on the requirement.

1 – Less Than: It will look for the largest value, either less than or equal to the lookup_value you have provided, and return the approximate match. It requires lookup_array to be arranged in ascending order (lower to higher or A to Z).

0 – Exact Match: It will look for and return the exact match to the lookup_value, irrespective of how the data is arranged or sorted.

-1 – Greater Match = This will search for the smallest value, either greater than or equal to the lookup_value you have provided and return the approximate match. It requires the lookup_array to be arranged in descending order (higher to lower or Z to A).

Key Takeaways
  • VBA MATCH function returns the position of the lookup value in the lookup range.
  • The VBA MATCH function output value is always a number. Hence, when we assign the output to a variable, we must always assign a numerical data type to the variable.
  • VBA MATCH function is used as a supporting function for the VLOOKUP function to return the column number dynamically based on the header.
  • VBA MATCH index is the position of the lookup value in the given range. The range could be row as well as column.
  • VBA MATCH Index can be used as a combined function to use as an alternative to the VLOOKUP function.

How to Use Match Function in VBA?

Let us show you a simple example of applying the VBA MATCH function with step-by-step approach.

For example, look at the following data in Excel.

VBA Match - Use -  Example

We have a product’s information from range A1 to B8. In cell D2, we have one of the product ids; for this, we must find the row number, i.e., in the “Product ID” column, we must find the position of the given Product ID entered in cell D2. Follow the steps listed below.

Step 1: Start the sub-procedure by naming the macro.

VBA Match - Use - Step 1

Step 2: Define a variable to assign the lookup value.

Use - Step 2

We have assigned a string data type because the VBA match string will be the lookup value.

Step 3: Assign the value in cell D2 for the defined variable.

VBA Match - Use - Step 3

Step 4: Define a variable to set the result cell.

Use - Step 4

Step 5: Set the result cell for the defined variable as cell E2.

Use - Step 5

Step 6: Now, apply the VBA MATCH function. Since we know where to store the result of the VBA MATCH function through the variable, let’s assign it to the target range variable.

Use - Step 6

Note: Since MATCH is a worksheet function, we must use the “WorksheetFunction” property to access it.

Step 7: The first argument of the VBA MATCH function is the lookup value. We have already assigned the lookup value to the variable “Lookup_Value.” Hence, we can directly enter the variable name here.

Use - Step 7

Step 8: The second argument is the lookup range, i.e., in which range we need to find the position of the given lookup value. In this case, we are looking for the product id hence the lookup range will be B2 to B8.

Use - Step 8

Step 9: The last argument is the matching type. We are looking forward to an exact match. Hence, we can give 0, which indicates the exact match in the VBA MATCH function. Following is the completed code, which you can copy and paste into your visual basic editor window.

Sub Match_Basic()
‘Define variable to set the loookup value entered in the cell D2
Dim Lookup_Value As String
‘Assign the lookup value from cell D2
Lookup_Value = Range(“D2”).Value
‘Define a varaible to set the result cell
Dim Target_Range As Range
‘Set the result cell as cell E2 where we need to store the result
Set Target_Range = Range(“E2”)
‘Assign the VBA MATCH function to target range variable
Target_Range = WorksheetFunction.Match(Lookup_Value, Range(“B2:B8”), 0)
End Sub

Let’s execute this code. We will get the position of the lookup value in the range B2 to B9.

VBA Match - Use - Step 9.jpg

Starting from cell B2, the lookup value “P129” position is 4. Hence the VBA MATCH function has returned the position of 4. The output given by the VBA MATCH function is always a whole number.


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.

Examples of VBA Match Function

Example #1 – VBA MATCH function as Supporting Function to VLOOKUP

Often VBA MATCH function plays a supporting role for the VLOOKUP excel function. VLOOKUP function is used to fetch the information of the lookup value from a specific column based on the column number entered. For example, look at the following data.

We have data in the range of A1 to D8. In the range G1 to J2, we have one of the invoice numbers for which we must retrieve the base amount, tax amount, and total amount from the data range A1 to D8.

VBA Match - Example 1

For example, look at the following VLOOKUP example.

VBA Match - Example 1 - vlookup

To fetch the “Base Amt,” we used the VLOOKUP function. For the base amount, the column number is 2. Hence, we have used the hard-coded 2 in the VLOOKUP function. However, we must automate getting the column number based on the header. To get this column number, we use the VBA MATCH function. The following code will fetch the column number dynamically using the VBA MATCH function.

Sub Match_Ex1()
‘Define variables to assign column numbers based on header value
Dim Basic_ColNo As Integer
Dim Tax_ColNo As Integer
Dim Total_ColNo As Integer
‘Apply VBA MATCH function to respective variable and find the position of each column name
Basic_ColNo = WorksheetFunction.Match(Range(“H1”).Value, Range(“A1:D1”), 0)
Tax_ColNo = WorksheetFunction.Match(Range(“I1”).Value, Range(“A1:D1”), 0)
Total_ColNo = WorksheetFunction.Match(Range(“J1”).Value, Range(“A1:D1”), 0)
‘Define a variable and set the complete data range
Dim Data_Range As Range
Set Data_Range = Range(“A1:D8”)
‘Apply VLOOKUP function with the help of MATCH function variables in column number argumnet
Range(“H2”).Value = WorksheetFunction.VLookup(Range(“G2”).Value, Data_Range, Basic_ColNo, 0)
Range(“I2”).Value = WorksheetFunction.VLookup(Range(“G2”).Value, Data_Range, Tax_ColNo, 0)
Range(“J2”).Value = WorksheetFunction.VLookup(Range(“G2”).Value, Data_Range, Total_ColNo, 0)
End Sub

When we execute this code, we will get the following result.

VBA Match - Example 1 - Output

The VBA MATCH function has dynamically returned the column number for the VLOOKUP function. Hence, we did not enter the hard-coded column numbers into the VLOOKUP function

Example #2 – VBA MATCH from Another Sheet

VBA MATCH function can also fetch the position of the lookup value from another sheet. Let’s use the same example data from the previous example. This time we got the data in one sheet and the requirement in another.

VBA Match - Example 2.jpg
VBA Match - Example 2 - Result Tab

Now we must fetch the required values from the data sheet to the result sheet. The VBA MATCH function needs to retrieve the column numbers from the datasheet to the result sheet. The following code will do the same.

Sub Match_Ex2()
‘Define variables to assign column numbers based on header value
Dim Basic_ColNo As Integer
Dim Tax_ColNo As Integer
Dim Total_ColNo As Integer
‘Apply VBA MATCH function to respective variable and find the position of each column name
‘Here we are fetching the column number from data sheet hence worksheet name mentioned as “Data”
Basic_ColNo = WorksheetFunction.Match(Range(“B1”).Value, Worksheets(“Data”).Range(“A1:D1”), 0)
Tax_ColNo = WorksheetFunction.Match(Range(“C1”).Value, Worksheets(“Data”).Range(“A1:D1”), 0)
Total_ColNo = WorksheetFunction.Match(Range(“D1”).Value, Worksheets(“Data”).Range(“A1:D1”), 0)
‘Define a variable and set the complete data range
‘The data range is in another sheet hence mentioned the worksheet name as “Data” then used range object
Dim Data_Range As Range
Set Data_Range = Worksheets(“Data”).Range(“A1:D8”)
‘Apply VLOOKUP function with the help of MATCH function variables in column number argumnet
Range(“B2”).Value = WorksheetFunction.VLookup(Range(“A2”).Value, Data_Range, Basic_ColNo, 0)
Range(“C2”).Value = WorksheetFunction.VLookup(Range(“A2”).Value, Data_Range, Tax_ColNo, 0)
Range(“D2”).Value = WorksheetFunction.VLookup(Range(“A2”).Value, Data_Range, Total_ColNo, 0)
End Sub

Let’s execute this code and get the result in the “Result” sheet.

VBA Match - Example 2 - Result

As we can see in the “Result” sheet we have got the values from “Data” sheet. Whenever we need to retrieve the values from another sheet, we need to use the worksheet name followed by the range.

Example #3 – VBA MATCH Function with Loops

So far, we have applied the VBA MATCH to one cell and got the result. However, we cannot write the formula for each cell when we have more cells. Hence, we must use loops to go through each cell and use the same code to retrieve values. For example, look at the following data in Excel.

VBA Match - Example 3

We have multiple cells to retrieve information, i.e., cells G2 to G4. In this case, we need to use the loop; the following code will use the “for next loops” and retrieve the information from the original data range, i.e., A1 to D8.

Sub Match_Ex3()
‘Define variables to assign column numbers based on header value
Dim Basic_ColNo As Integer
Dim Tax_ColNo As Integer
Dim Total_ColNo As Integer
‘Apply VBA MATCH function to respective variable and find the position of each column name
‘Here we are fetching the column number from data sheet hence worksheet name mentioned as “Data”
Basic_ColNo = WorksheetFunction.Match(Range(“H1”).Value, Worksheets(“Data”).Range(“A1:D1”), 0)
Tax_ColNo = WorksheetFunction.Match(Range(“I1”).Value, Worksheets(“Data”).Range(“A1:D1”), 0)
Total_ColNo = WorksheetFunction.Match(Range(“J1”).Value, Worksheets(“Data”).Range(“A1:D1”), 0)
‘Define a variable and set the complete data range
‘The data range is in another sheet hence mentioned the worksheet name as “Data” then used range object
Dim Data_Range As Range
Set Data_Range = Worksheets(“Data”).Range(“A1:D8”)
‘Define variable to find the last used row
Dim LR As Long
LR = Cells(Rows.Count, 7).End(xlUp).Row
‘Define a variable to use for next loop
Dim k As Long
For k = 2 To LR
Cells(k, 8).Value = WorksheetFunction.VLookup(Cells(k, 7).Value, Data_Range, Basic_ColNo, 0)
Cells(k, 9).Value = WorksheetFunction.VLookup(Cells(k, 7).Value, Data_Range, Tax_ColNo, 0)
Cells(k, 10).Value = WorksheetFunction.VLookup(Cells(k, 7).Value, Data_Range, Total_ColNo, 0)
Next k
End Sub

Once we run the code, it will retrieve all the listed invoice numbers’ values.

Example 3 - Output

Important Things to Note

  • VBA MATCH is a worksheet function. Hence, we must access it using the worksheet function property.
  • VBA MATCH function will return an error value #N/A (Not Available) if the lookup value is not the same as the in the lookup range.
  • When we want to apply the VBA Match multiple criteria, we must concatenate multiple values in the lookup value as well as in the lookup range.

VBA MATCH function is not a case-sensitive formula. For example, the texts “Excel” and “Excel” are identical.

Frequently Asked Questions (FAQs)

1. How to use INDEX MATCH function in Excel VBA?

Combining INDEX and MATCH can be an alternative to the VLOOKUP function. For example, look at the following data in Excel. For example, look at the following data in Excel.

VBA Match - FAQ 1

We have data in the range of A1 to B8. We have one lookup value (city code) in cell D2, and we must retrieve the sales value for this city code in cell E2.

The following INDEX and MATCH will retrieve the sale information.

Sub Match_FAQ()
‘Define a varaible to find the position of the lookup value through MATCH function
Dim Match_Function_Result As Integer
Match_Function_Result = WorksheetFunction.Match(Range(“D2”).Value, Range(“A2:A8”), 0)
‘Apply index function along with MATCH function variable to find the sales value
Range(“E2”).Value = WorksheetFunction.Index(Range(“B2:B8”), Match_Function_Result)
End Sub

FAQ 1 - Output

2. How to match dates in VBA?

To match dates in VBA, we need to have the same date format in both the lookup value and lookup range.

3. Why is VBA match function not working?


• VBA Match function returns the output as an integer; hence, the variable data type should be an integer or any other valid numerical data type. If the data type is anything other than numerical, it will throw an error.

• VBA Match function will return #N/A error if the lookup value is not the same as the one in the lookup range.

4. How to handle match error VBA?

To handle match errors, we can use the “On Error Goto” statement and show an error message to the user about the error type.

Download Template

This article must help understand VBA Match with its formulas and examples. You can download the template here to use it instantly.

Guide to VBA Match. Here we apply match function in VBA to find the lookup value along with example & downloadable excel template. You can learn more from the following articles –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *