How to Use the Excel VBA INDEX MATCH Function in Another Worksheet – 3 Methods

Code Breakdown

Sub IndexMatch()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim result As Variant

• defines the variables used in the subroutine; defines another variable to hold the outcome of the index match operation, and two more variables to hold the worksheet objects.

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

• the worksheet objects are assigned to the correct worksheets. In this instance; “Sheet1” is set to the ws1 variable, and “Sheet2” 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))

• carries out the index match operation across the two worksheets: sets the outcome of the index match operation as the value of C6 in worksheet 2 (ws2). The lookup array is B5:D16 in worksheet 1 (ws1). C4 in worksheet 2 (ws2) and cell C5 in worksheet 2 (ws2) are the match values for row and column. The “0” argument indicates an exact match.

End Sub

• ends the Subroutine.

Code Breakdown

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

• defines variables that will be used later: target name, target ID, and quantity columns,  sheet, table, and column names.

  Set iSheet = ThisWorkbook.Sheets("Sheet1")
  Set iTable = iSheet.ListObjects("SalesData")

• the sheet and the table are searched with the Set function. Here, “Sheet1” and “SalesData”.

  TargetName = "John"
  NameColumn = iTable.ListColumns("Name").Index
  QuantityColumn = iTable.ListColumns("Quantity").Index

• the target name is set to “John,” and the NameColumn and QuantityColumn variables are used to store the column indexes for the name and quantity columns.

  iValue = iTable.DataBodyRange.Value

• the values in the table are collected 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

• to determine whether the target name is present in the NameColumn, the code loops through each row in 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

• the If statement displays a message box with name and quantity if the iResult is True. A message box is also displayed indicating the iResult is False.

Code Breakdown

Function GetQuant(targetName As String) As Variant

• defines the GetQuant function, which accepts only one argument: 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 find the value included in these variables.

  Set iSheet = ThisWorkbook.Sheets("Sheet1")
  Set iTable = iSheet.ListObjects("SalesData")

• references “Sheet1” and “SalesData” to the variables “iSheet” and “iTable,”.

  NameColumn = iTable.ListColumns("Name").Index
  QuantityColumn = iTable.ListColumns("Quantity").Index

• determines the indexes for “Name” and “Quantity” columns in iTable.

  iValue = iTable.DataBodyRange.Value

•  the array of value sin the data body range of  iTable is assigned to the iValue variable.

  For iCount = 1 To UBound(iValue)
    If iValue(iCount, NameColumn) = targetName Then
      iResult = True
      Exit For
    End If
  Next iCount

• 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 value of the iResult variable is True.

End Function