The topic of this Excel blog post is how to look up data from another worksheet in the same workbook using the INDEX MATCH function in VBA code. A common technique for searching for specific values in a table or range is the INDEX MATCH function, which can be automated with VBA. For readers who want to use this technique in their own Excel workbooks, the post “How to Apply INDEX MATCH from Another Worksheet in Excel VBA” is offering detailed instructions and sample codes. Here’s an overview below-
How to Apply INDEX MATCH from Another Worksheet in Excel VBA: 3 Methods
In this Excel blog post, you are going to see a dataset of Sale Quantity corresponding to different Sellers’ Names. The dataset also contains dates of particular sales. In case you have so many data sets and want to know about a particular seller’s Quantity of sales this post is going to be very helpful for you. Like in the following images, the dataset sheet is sheet1 and the result sheets are sheet2 & sheet 3.
1. Using the Worksheet Functions in VBA
We frequently need to carry out intricate calculations or lookups based on information from multiple worksheets. Utilizing the potent union of the INDEX and MATCH functions is one approach to accomplishing this. We’ll look at how to conduct lookups across multiple worksheets using VBA‘s INDEX MATCH functions in this section of the blog post. Here, we’ll search for the quantity sold by Bill.
📌 Steps:
- First, you need to launch VBA and insert a Module in Microsoft Excel.
- After that, copy the code below and paste it into the code window.
Sub IndexMatch()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim result As Variant
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
ws2.Range("C6").Value = Application.WorksheetFunction.Index(ws1.Range("B5:D16"), Application.WorksheetFunction.Match(ws2.Range("C4"), ws1.Range("B5:B16"), 0), Application.WorksheetFunction.Match(ws2.Range("C5"), ws1.Range("B4:D4"), 0))
End Sub
- To run the code, press F5 on your keyboard or choose Run from the menu bar.
- Look at the image below to see the outcome following code execution.
VBA Explanation:
Sub IndexMatch()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim result As Variant- We defined the variables used in the subroutine in the first section. Then, we defined another variable to hold the outcome of the index match operation, and two more variables to hold worksheet objects.
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")- We assigned the worksheet objects to the correct worksheets in the workbook by these lines of code. In this instance, we set the “Sheet1” worksheet to the ws1 variable, and the “Sheet2” worksheet to the ws2 variable.
ws2.Range("C6").Value = Application.WorksheetFunction.Index(ws1.Range("B5:D16"), Application.WorksheetFunction.Match(ws2.Range("C4"), ws1.Range("B5:B16"), 0), Application.WorksheetFunction.Match(ws2.Range("C5"), ws1.Range("B4:D4"), 0))- This line carried out the index match operation across the two worksheets by this line of code. To accomplish this, it sets the outcome of the index match operation as the value of cell C6 in worksheet 2 (ws2). We defined the lookup array as the range B5:D16 in worksheet 1 (ws1). Then, we used Cell C4 in worksheet 2 (ws2) and cell C5 in worksheet 2 (ws2) as the match values for the row and column, respectively. The last “0” argument indicates that an exact match is necessary.
End Sub- Ending the Subroutine.
2. Returning MATCH Value from an Excel Table
In this method of the article, you will see Sellers’ Names and Sale Quantity corresponding to that Seller in a message box. No matter which sheet you are in, you are going to see that message box after running the VBA code.
📌 Steps:
- Select the data range B4:D17 and press Ctrl+T.
- Create Table window will appear. Mark the box beside My table has headers and click OK.
- It is important to rename your latest created table from the source sheet as you need the name in the VBA code. So you need to rename the table from Table Design>>Properties>>Table Name: Sales Data.
- After making the table copy the code from below and paste it into the VBA Module. Now you are ready to Run the VBA code.
Sub ReturnMatchedResultByIndex()
Dim iSheet As Worksheet
Dim iTable As Object
Dim iValue As Variant
Dim TargetName As String
Dim IdColumn As Long
Dim NameColumn As Long
Dim QuantityColumn As Long
Dim iCount As Long
Dim iResult As Boolean
Set iSheet = ThisWorkbook.Sheets("Sheet1")
Set iTable = iSheet.ListObjects("SalesData")
TargetName = "John"
NameColumn = iTable.ListColumns("Name").Index
QuantityColumn = iTable.ListColumns("Quantity").Index
iValue = iTable.DataBodyRange.Value
For iCount = 1 To UBound(iValue)
If iValue(iCount, NameColumn) = TargetName Then
iResult = True
Exit For
End If
Next iCount
If iResult Then
MsgBox "Name: " & TargetName & vbLf & "Quantity: " & iValue(iCount, QuantityColumn)
Else
MsgBox "Name: " & TargetName & vbLf & "Quantity Not found"
End If
End Sub
- Message Box will appear on the screen where Name: John and Quantity= 5 are displayed just like in the following image.
VBA Explanation:
Sub ReturnMatchedResultByIndex()
Dim iSheet As Worksheet
Dim iTable As Object
Dim iValue As Variant
Dim TargetName As String
Dim IdColumn As Long
Dim NameColumn As Long
Dim QuantityColumn As Long
Dim iCount As Long
Dim iResult As Boolean- The first lines of code define variables that will be used later. The target name, target ID, and quantity columns are among the variables in this list, along with the sheet, table, and column names.
Set iSheet = ThisWorkbook.Sheets("Sheet1")
Set iTable = iSheet.ListObjects("SalesData")- The sheet and table are then set by the code to be searched with the Set function. In this instance, it’s setting the “Sheet1” sheet and the “SalesData” table.
TargetName = "John"
NameColumn = iTable.ListColumns("Name").Index
QuantityColumn = iTable.ListColumns("Quantity").Index- The target name is then set to “John,” and the NameColumn and QuantityColumn variables are used to store, respectively, the column indexes for the name and quantity columns.
iValue = iTable.DataBodyRange.Value- The values are then collected by the code from the table’s data body range and placed in an array variable called iValue.
For iCount = 1 To UBound(iValue)
If iValue(iCount, NameColumn) = TargetName Then
iResult = True
Exit For
End If
Next iCount- After that, in order to determine whether the target name is present in the NameColumn, the code then loops through each row of the array. The iResult variable is set to True and the loop is ended using the Exit For statement if the target name is found.
If iResult Then
MsgBox "Name: " & TargetName & vbLf & "Quantity: " & iValue(iCount, QuantityColumn)
Else
MsgBox "Name: " & TargetName & vbLf & "Quantity Not found"
End If
End Sub- To determine whether iResult is True, the code uses an If statement. If so, a message box containing the target name’s name and quantity value is shown. A message box indicating that is displayed if iResult is False.
3. Using User-Defined Function
You can use a user-defined function (UDF) to extract matched values from a dataset. We will give the procedure in the following steps. We will enter the seller’s Name and Quantity, and the function will return the Quantity.
📌 Steps:
- In the first step of creating a User Defined Function, you have to copy the following code and paste it into the Module.
- Remember don’t Run this code rather save it and just return to your worksheet.
Function GetQuant(targetName As String) As Variant
Dim iSheet As Worksheet
Dim iTable As Object
Dim iValue As Variant
Dim NameColumn As Long
Dim QuantityColumn As Long
Dim iCount As Long
Dim iResult As Boolean
Set iSheet = ThisWorkbook.Sheets("Sheet1")
Set iTable = iSheet.ListObjects("SalesData")
NameColumn = iTable.ListColumns("Name").Index
QuantityColumn = iTable.ListColumns("Quantity").Index
iValue = iTable.DataBodyRange.Value
For iCount = 1 To UBound(iValue)
If iValue(iCount, NameColumn) = targetName Then
iResult = True
Exit For
End If
Next iCount
If iResult Then
GetQuant = iValue(iCount, QuantityColumn)
Else
GetQuant = "Quantity Not found"
End If
End Function- Then, save the codes by clicking the Save icon.
- After returning to the worksheet, insert “=” and start to type the function name. You will see Excel is showing your newly created function in the suggestions.
- Now suppose you need to see the quantity from another sheet. So enter a formula with the GetQuant function and you will be able to see the quantity in another sheet.
=GetQuant(Sheet1!B6)Here,
The function GetQuant will return the corresponding Quantity of Cell B6 in Sheet1.
VBA Explanation:
Function GetQuant(targetName As String) As Variant- This is the definition of a function called GetQuant, which accepts just one argument of type String named targetName and returns a data type of Variant.
Dim iSheet As Worksheet
Dim iTable As Object
Dim iValue As Variant
Dim NameColumn As Long
Dim QuantityColumn As Long
Dim iCount As Long
Dim iResult As Boolean- The worksheet, table, array of values, and other variables needed to find the value are all included in these definitions of the variables.
Set iSheet = ThisWorkbook.Sheets("Sheet1")
Set iTable = iSheet.ListObjects("SalesData")- These lines give the references to the sheets “Sheet1” and “SalesData” to the variables “iSheet” and “iTable,” respectively.
NameColumn = iTable.ListColumns("Name").Index
QuantityColumn = iTable.ListColumns("Quantity").Index- We used these two lines to determine the indexes for the iTable table’s “Name” and “Quantity” columns.
iValue = iTable.DataBodyRange.Value- Then, we assigned the array of values from the data body range of the iTable table to the iValue variable using this line.
For iCount = 1 To UBound(iValue)
If iValue(iCount, NameColumn) = targetName Then
iResult = True
Exit For
End If
Next iCount- This line starts a loop that iterates over each row in the iValue array from 1 to the number of elements in the array.
If iResult Then
GetQuant = iValue(iCount, QuantityColumn)
Else
GetQuant = "Quantity Not found"
End If- The GetQuant variable receives the value of the “Quantity” column in the current row if the iResult variable’s value is True.
End FunctionFrequently Asked Questions (FAQs)
1. What is INDEX MATCH in Excel?
We can use the Excel formula combination INDEX MATCH to look up and retrieve data from a particular table. It serves as a substitute for the widely used VLOOKUP formula.
2. What is the benefit of using INDEX MATCH in Excel VBA?
You can automate the lookup and retrieval of data from a table using Excel VBA‘s INDEX MATCH function, which can reduce errors and save time when compared to performing the same task manually.
3. How do you use INDEX MATCH in Excel VBA?
You can define the lookup range and the lookup values as variables in Excel VBA by using the Application.WorksheetFunction.Index and Application.WorksheetFunction.Match.
Download Practice Workbook
Conclusion
Excel VBA’s INDEX MATCH function can be a useful tool for automating data extraction and lookup procedures in your workbooks. With the aid of VBA, you can design unique functions and macros that can manage tricky lookup situations and dynamically retrieve information from different worksheets or workbooks.
