VBA Index Match

Index Match in VBA

INDEX & MATCH function in VBA combination is the alternative to VLOOKUP function in excel. In VBA we don’t the luxury of using INDEX & MATCH function directly because these two functions are not part of the VBA built-in functions. However, we can still use them as part of the worksheet function class.

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

For an example look at the below data.

In the above data, 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 lookup value column is thereafter the result column. In this case, VLOOKUP cannot fetch the salary amount because VLOOKUP works only from right to left not from left to right.

In these cases, we need to use the combination formula of VBA INDEX & MATCH function. Let’s perform the task of finding the salary amount of each department in the VBA code.

Step 1: Start the sun routine.

Step 2: Declare the VBA variable as Integer.

Code:

Sub INDEX_MATCH_Example1()

Dim k As Integer

End Sub

Step 3: Now open For Next Loop in VBA.

Code:

Sub INDEX_MATCH_Example1()

Dim k As Integer

For k = 2 To 5


Next k

End Sub

Step 4: Inside the VBA loop 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 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 function 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 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 the earlier example we cannot manually supply the row number every time. So use the MATCH function.

In order 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 LOOKUP value, here our lookup value is department names, it is there in the cells (2, 4).

Since every time 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 result in column 5.

So, we got the result.

We can use this formula as an alternative to the VLOOKUP function.

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 template. Below are some useful excel articles related to VBA –

• ScreenUpdating to Run your VBA Code Faster
• Delete Excel Worksheet in VBA Excel
• Excel VBA Goal Seek
• String Array in VBA
• VBA Match Function
• User Defined Function in VBA
• Arrays Function in Excel VBA
• String Functions in VBA