What is Index Match in VBA Excel?
With the help of the VBA Index Match, you can look for a particular value in a table or range and get the matching value from a different column or range. The Index and Match functions in VBA can be used to accomplish the same goal programmatically. It enables you to use custom VBA code to automate tasks and manipulate data in Excel.
The VBA code in this example is called “IndexMatchExample.” This code looks for Lee’s location in Excel data using VBA Index Match and shows the result in a message box. The first step is to look for the name “Lee” in Column A (A2:A5) within a particular range. If a match is found, the VBA Index function is used to obtain the matching value from Column B (B2:B5). The “result” variable contains the outcome.
After checking for errors, the code shows a message box with the found value, such as “Value found: [result].” if there are no errors. But if there isn’t a match, it displays the message “Value not found.”
Table of contents
Key Takeaways
- You can programmatically search and retrieve data from Excel worksheets using VBA Index Match.
- Basic lookups and more complicated tasks involving numerous criteria and external workbooks can both be accomplished with VBA Index Match.
- When utilizing VBA Index Match, proper error handling and data validation are crucial.
- To perform Index Match across different worksheets within the same workbook, we can use VBA Index Match from Another Worksheet.
How to Use Index Match in VBA?
- Open Excel and press ALT + F11 to open the VBA editor.
- Insert a new module by clicking on “Insert” in the menu and selecting “Module.”
- First, declare the variables you will need for your VBA Index Match operation.
For example:
Dim searchValue As String ‘ Variable to store the value you want to search for
Dim result As Variant ‘ Variable to store the result of the Index Match - Next, assign the value you want to search for to the “searchValue” variable:
searchValue = “YourSearchValue”
Replace “YourSearchValue” with the actual value you want to find in the Excel worksheet. - Now, use the “Application.Index” and “Application.Match” functions to perform the Index Match operation.
For example:
result = Application.Index(Range(“ColumnToRetrieveDataFrom”), Application.Match(searchValue, Range(“ColumnToSearchIn”), 0))
• Range(“ColumnToRetrieveDataFrom”): Specify the range from which you want to retrieve data based on the match.
• Range(“ColumnToSearchIn”): Specify the range in which you want to search for the searchValue.
0, as the last argument indicates an exact match. You can use 1 for the approximate match as well. - If a match is found, you can display or use the retrieved result.
For example:
MsgBox “Value: ” & result - Save your VBA code and run the macro by pressing F5 or selecting it from the Macro dialog box in Excel.
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
Example #1
In this example, we will locate and show ‘John’s’ salary in Excel within the same salary cell using the VBA Index Match.
- Step 1: In the new module, we begin with the creation of a subroutine, after which two variables are declared: “rowIndex” to hold the match result and “employeeName” to store the name we wish to look up.
- Step 2: We then assign the name “John” to the employeeName variable. You can replace this with the name you want to search for.
- Step 3: In this line, we use “Application.Match” to search for “employeeName” in the range Sheets(“Example_1”).Range(“A2:A5”), and the 0 indicates an exact match.
The result is stored in the “rowIndex” variable.
Step 4: Here, we check if “rowIndex” contains an error using the IsError function.
- Step 5: If “rowIndex” does not contain an error, we set the value of a cell to display the salary.
The “Application.Index” function retrieves the salary from column B (Range(“B2:B5”)) of the row where the match was found (rowIndex + 1).
- Step 6: If there was an error (employee not found), we display a message box indicating that the employee was not found.
- Step 7: Save the VBA macro and click on run.
If the employee “John” is found in “Example_1,” the code will search for him and display “Salary: [John’s salary]” in Cell B2, which is the adjacent cell in Column B.
Otherwise, it shows a message box in VBA saying “Employee not found.”
Here is the full code:
Sub BasicIndexMatch()
Dim employeeName As String
Dim rowIndex As Variant
employeeName = “John”
rowIndex = Application.Match(employeeName, Sheets(“Example_1”).Range(“A2:A5”), 0)
If Not IsError(rowIndex) Then
Cells(rowIndex + 1, 2).Value = “Salary: ” & Application.Index(Sheets(“Example_1”).Range(“B2:B5”), rowIndex)
Else
MsgBox “Employee not found.”
End If
End Sub
Example #2
In this example, we will use the VBA Match Index with a criterion to search for ‘Monitor’ in the ‘SalesData’ sheet and display the corresponding value.
- Step 1: We start by creating a new subroutine and declaring the following two variables:
- searchValue: This variable will store the value we want to search for in the Excel data.
- result: This variable will store the result of the Index Match operation.
- Step 2: Here, we assign the value “Monitor” to the searchValue variable. It is the value we want to find in the Excel data.
- Step 3: This is where the main Index Match operation occurs:
“Application.Match(searchValue, Sheets(“SalesData”).Range(“A2:A5”), 0)”:
This section of the code carries out the Match operation. It looks for the value in searchValue (“Monitor”) in the “SalesData” worksheet’s range A2:A5. The final argument, which is 0, denotes a perfect match.
“Application.Index(Sheets(“SalesData”).Range(“B2:B5”), … )”:
This section of the code carries out the Match operation. Based on the row where the match was found in column A, it searches the “SalesData” worksheet for the corresponding value in the range B2:B5.
- Step 4: The “If” statement checks whether the result contains an error. If not, it means that the Index Match operation found a matching value.
- Step 5: This line shows a message box with the value “Value: ” followed by the outcome of the VBA Index Match operation if the result is error-free, meaning a match was found.
For example, the message box will say “Value: $200” if “Monitor” is located and the matching value in column B is $200.
- Step 6: If the result contains an error (i.e., no match was found), this part of the code displays a message box with the text “Value not found.”
- Step 7: Save the macro and click on run.
‘Value: [Monitor’s corresponding value]’ will appear in a message box when you run the code if “Monitor” is found.
Else, it will show ‘Value not found.
Here is the full code:
Sub IndexMatchWithValue()
Dim searchValue As String
Dim result As Variant
searchValue = “Monitor”
result = Application.Index(Sheets(“SalesData”).Range(“B2:B5”), Application.Match(searchValue, Sheets(“SalesData”).Range(“A2:A5”), 0))
If Not IsError(result) Then
MsgBox “Value: ” & result
Else
MsgBox “Value not found.”
End If
End Sub
Example #3
In this example, to search for particular criteria in Sheet 3 of the current workbook, i.e., “VBA Index Match.xlsm,” we use VBA Index Match from another workbook to retrieve pertinent data from “Workbook2,” which is a different Excel workbook.
Workbook1
Workbook2
- Step 1: In the new module, we start by creating a subroutine and assigning two following variables:
- criteria: This variable will store the criteria (value to search for) obtained from the current workbook.
- rowIndex: This variable will store the result of the Match operation.
- Step 2: Here, we retrieve the criteria (the value to search for) from cell A2 of “Index_Match_Example” in the current workbook (ThisWorkbook).
- Step 3: This is where the main Index Match operation occurs.
The Match operation is carried out by this section of the code. Within the given range (A2:A100), it looks for the criteria value that was previously obtained in “Sheet1” of the external workbook “Workbook2.xlsx.” The final argument, which is 0, denotes a perfect match.
- Step 4: This If statement checks whether “rowIndex” contains an error. If rowIndex does not contain an error, it means that the Index Match operation found a matching row.
- Step 5: When a match is found and “rowIndex” is not containing an error, this line shows a message box that says “Data from Workbook2: ” followed by the value that is in column B of the corresponding row in “Sheet1” of “Workbook2.xlsx.”
To account for the header row in excel, it retrieves the data using the rowIndex plus 1.
- Step 6: If “rowIndex” contains an error (i.e., no match was found), this part of the code displays a message box with the text “Data not found in Workbook2.”
- Step 7: Save the macro and click on run.
After you run the code and if a match is found, the code displays ‘Data from Workbook2: [Retrieved data].
If there is no match found, it will show as “Data not found in Workbook2.”
Here is the full code:
Sub IndexMatchFromAnotherWorkbook()
Dim criteria As String
Dim rowIndex As Variant
criteria = ThisWorkbook.Sheets(“Index_Match_Example”).Cells(2, 1).Value
rowIndex = Application.Match(criteria, Workbooks(“Workbook2.xlsx”).Sheets(“Sheet1”).Range(“A2:A100”), 0)
If Not IsError(rowIndex) Then
MsgBox “Data from Workbook2: ” & Workbooks(“Workbook2.xlsx”).Sheets(“Sheet1”).Cells(rowIndex + 1, 2).Value
Else
MsgBox “Data not found in Workbook2.”
End If
End Sub
Important Things to Note
- Excel’s VBA Index Match offers a strong and adaptable way to perform data lookups and retrievals.
- To further narrow down your search, you can use VBA Index Match combine multiple criteria conditions using logical operators like “AND” and “OR” within the VBA code.
- You can automate time-consuming Index Match operations over sizable datasets or carry out several lookups in succession by using the VBA Index Match loop.
Frequently Asked Questions (FAQs)
It is possible to use VBA Index Match with non-contiguous ranges. To get the desired data, you can define multiple ranges and use the Match and Index functions for each range independently.
Yes, you can perform more complex operations by nesting VBA Index Match within other VBA functions and procedures. For instance, you can use Index Match in conjunction with conditional statements or inside of a loop.
It is possible to use VBA Index Match with dynamic ranges. Using VBA, you can dynamically calculate the range based on predefined criteria, or you can define dynamic named ranges.
Using VBA for Index Match offers several advantages:
• You can save time and effort by automating tasks and performing VBA Index Match operations programmatically.
• Complex logic and conditional operations can be implemented with flexibility using VBA, which can be difficult to accomplish with Excel formulas alone.
• VBA is an effective tool for data manipulation and analysis because it can be integrated with other Excel features and external data sources.
• Using VBA, you can design unique user-defined functions (UDFs) to fulfill particular needs that conventional Excel formulas might not be able to.
Download Template
This article must be helpful to understand the VBA Index Match, with formulas and examples. You can download the templates here to use it instantly.
Recommended Articles
This has been a guide to VBA Index Match. Here we learn to use combination of Index & Match functions in Excel VBA code, with examples & points to remember. You can learn more from the following articles –
Leave a Reply