WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA Index Match

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 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.

VBA Index Match

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

You can download this VBA Index Match Excel Template here – VBA Index Match Excel Template

For example, look at the below data.

VBA Index Match Example 1

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 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 & 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 Integer variable.

Code:

Sub INDEX_MATCH_Example1()

Dim k As Integer

End Sub

VBA Index Match Example 1-1

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

VBA Index Match Example 1-2

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

VBA Index Match Example 1-3

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 VBA class, so open the worksheet function class.

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Code:

Sub INDEX_MATCH_Example1()

Dim k As Integer

For k = 2 To 5

Cells(k, 5).Value = WorksheetFunction.

Next k

End Sub

VBA Index Match Example 1-4

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

VBA Index Match Example 1-5

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

 Example 1-6

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

Example 1-7

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

Example 1-8

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.

Example 1-9

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

  • VBA Value
  • String Array in VBA
  • VBA Match Function
  • Arrays Function in VBA
0 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA Index Match Excel Template

New Year Offer - VBA Training Course (6 courses, 35+ hours video) View More