# VBA Index Match

Published on :

21 Aug, 2024

Blog Author :

N/A

Edited by :

Aaron Crowe

Reviewed by :

Dheeraj Vaidya

## Index Match in VBA

The INDEX and MATCH function in VBA combination is the alternative to the VLOOKUP function in excel. In VBA, we do not have the luxury of using the INDEX and 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.

##### Table of contents

### 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. Based on this department name, we need to extract the salary amount.

But the problem here is that the result column is there in the first, and the lookup value column is the result column. So, 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 the VBA INDEX and MATCH function. But, first, let us 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 Integer variable.

**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 5th 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: **We must apply that cell's VBA INDEX and MATCH formula. As we said, we need to use these functions as a Worksheet Function in VBA 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 the worksheet function in VBA, you must be sure of the arguments of the formula. The first argument is an 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 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: **The MATCH function's 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 changes, 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

We have now completed 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 function.

### Recommended Articles

This article is a guide to VBA Index Match. Here, we learn how to use the Index Match function in VBA as an alternative to VLOOKUP, examples, and download templates. Below are some useful Excel articles related to VBA: -