VBA Index Match

Index Match in VBA

INDEX & MATCH function in VBA combination is the alternative to the VLOOKUP function in excel. In VBA, we don’t have the luxury of using the INDEX & 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 directly because these two functions are not part of the VBA built-in functionsVBA Built-in FunctionsVBA functions serve the primary purpose to carry out specific calculations and to return a value. Therefore, in VBA, we use syntax to specify the parameters and data type while defining the function. Such functions are called user-defined functions.read more. However, we can still use them as part of the worksheet function class.

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 Index Match (wallstreetmojo.com)

How to Use Index Match in VBA? (Step by Step)

For example, look at the below data.

In the above data, the lookup value is the Department name, and based on this department name, we need to extract the salary amount.

But the problem here is the result column is there in the first, and the lookup value column is thereafter the result column. In this case, VLOOKUP cannot fetch the salary amount because VLOOKUP worksVLOOKUP WorksThe 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 only from right to left, not from left to right.

In these cases, we need to use the combination formula of the VBA INDEX & MATCH function. Let’s perform the task of finding the salary amount of each department in the VBA codeVBA CodeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more.

Step 1: Start the sun routine.

Step 2: Declare the VBA IntegerVBA IntegerIn VBA, an integer is a data type that may be assigned to any variable and used to hold integer values. In VBA, the bracket for the maximum number of integer variables that can be kept is similar to that in other languages. Using the DIM statement, any variable can be defined as an integer variable.read more variable.

Code:

Sub INDEX_MATCH_Example1()

Dim k As Integer

End Sub

Step 3: Now open For Next Loop in VBAFor Next Loop In VBAAll programming languages make use of the VBA For Next loop. After the FOR statement, there is a criterion in this loop, and the code loops until the criteria are reached. read more.

Code:

Sub INDEX_MATCH_Example1()

Dim k As Integer

For k = 2 To 5


Next k

End Sub

Step 4: Inside the VBA loopVBA LoopA VBA loop in excel is an instruction to run a code or repeat an action multiple times.read more, execute the formula. In the 5^th column, we need to apply the formula, so the code is CELLS (k,5).Value =

Code:

Sub INDEX_MATCH_Example1()

Dim k As Integer

For k = 2 To 5

Cells(k, 5).Value =

Next k

End Sub

Step 5: In that cell, we need to apply the VBA INDEX & MATCH formula. As I told, we need to use these functions as Worksheet Function in VBAWorksheet Function In VBAThe 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 class, so open the worksheet function class.

Code:

Sub INDEX_MATCH_Example1()

Dim k As Integer

For k = 2 To 5

Cells(k, 5).Value = WorksheetFunction.

Next k

End Sub

Step 6: After entering the worksheet function class, we can see all the available worksheet functions, so select the INDEX function.

Code:

Sub INDEX_MATCH_Example1()

Dim k As Integer

For k = 2 To 5

Cells(k, 5).Value = WorksheetFunction.Index(

Next k

End Sub

Step 7: While using worksheet function in VBA, you need to be absolutely sure of the arguments of the formula. The first argument is array i.e., from which column we need the result. In this case, we need the result from A2 to A5.

Code:

Sub INDEX_MATCH_Example1()

Dim k As Integer

For k = 2 To 5

Cells(k, 5).Value = WorksheetFunction.Index(Range("A2:A5"),

Next k

End Sub

Step 8: Next up is from which row number we need the result. As we have seen in the earlier example, we cannot manually supply the row number every time. So use the MATCH function.

To use the MATCH function once again, we need to open the Worksheet Function class.

Code:

Sub INDEX_MATCH_Example1()

Dim k As Integer

For k = 2 To 5

Cells(k, 5).Value = WorksheetFunction.Index(Range("A2:A5"),
WorksheetFunction.Match(

Next k

End Sub

Step 9: MATCH functions first argument is the LOOKUP value; here, our lookup value is department names; it is there in the cells (2, 4).

Since every time the row number has to change, we can supply the variable “k” in place of manual row number 2. Cells (k, 4).Value

Code:

Sub INDEX_MATCH_Example1()

Dim k As Integer

For k = 2 To 5

Cells(k, 5).Value = WorksheetFunction.Index(Range("A2:A5"),
WorksheetFunction.Match(Cells(k,5).Value,

Next k

End Sub

Step 10: Next, we need to mention the department value range i.e., Range (“B2:B5”).

Code:

Sub INDEX_MATCH_Example1()

Dim k As Integer

For k = 2 To 5

Cells(k, 5).Value = WorksheetFunction.Index(Range("A2:A5"),
WorksheetFunction.Match(Cells(k,5).Value,Range("B2:B5"),

Next k

End Sub

Step 11: Next, put the argument as 0 because we need an exact match and close the brackets.

Code:

Sub INDEX_MATCH_Example1()

Dim k As Integer

For k = 2 To 5

Cells(k, 5).Value = WorksheetFunction.Index(Range("A2:A5"),
WorksheetFunction.Match(Cells(k, 4).Value, Range("B2:B5"), 0))

Next k

End Sub

Ok, we are done with the coding part. Let us run the code to have the result in column 5.

So, we got the result.

We can use this formula as an alternative to the VLOOKUP functionAlternative To The VLOOKUP FunctionTo reference data in columns from right to left, we can combine the index and match functions, which is one of the best Excel alternatives to Vlookup.read more.

Recommended Articles

This has been a guide to VBA Index Match. Here we learn how to use the Index Match Function in VBA as an alternative to VLOOKUP along with Examples and download templates. Below are some useful excel articles related to VBA –

• VBA Value
• String Array in VBA
• VBA Match Function
• Arrays Function in VBA