How to Use Excel VBA INDEX MATCH with Array

VBA Breakdown

Sub Index_Match_1D_Array()

• This is the start of a Subroutine called Index_Match_1D_Array.

On Error GoTo Txt

• This is an error-handling statement. It tells VBA to go to a specific line of code if an error occurs. In this case, if an error occurs, it will jump to the Txt label at the end of the subroutine.

    Dim myArr() As Variant
    Dim Name As Range, Physics As Range
    Dim search_name, score As String

• This declares some variables that will be used later in the subroutine. myArr is an array, Name, and Physics are ranges, and search_name and score are strings.

    Set Name = Sheets("Index_Match").Range("B5:B14")
    Set Physics = Sheets("Index_Match").Range("C5:C14")

• This sets the values of the Name and Physics variables to the ranges B5:B14 and C5:C14 on the worksheet called Index_Match.

    search_name = InputBox("Physics Score" & vbNewLine & "Enter the name of the student:")

• This prompts the user to input the name of a student.

    ReDim myArr(1 To 1)
    myArr(1) = search_name

• This creates a one-dimensional array called myArr with one element and sets that element to the value of search_name. 

    With Application.WorksheetFunction
        score = .index(Physics, .match(myArr(1), Name, 0))
    End With

• This uses the INDEX and MATCH functions to find the score of the student entered by the user. The INDEX function looks up the value in the Physics range that corresponds to the position of the student’s name in the Name range (found using the MATCH function). The result is stored in the score variable.

    MsgBox (myArr(1) & " has scored " & score & " in Physics")

• This displays a MsgBox with the name of the student entered by the user and their score in Physics.

    Exit Sub

• This exits the subroutine.

Txt:
    MsgBox "Not Found"
End Sub

• This is the label that the On Error GoTo statement will jump to if an error occurs. In this case, it displays a MsgBox saying “Not Found“.

VBA Breakdown

Sub Index_Match_2D_Array()

• The Subroutine is declared with the name Index_Match_2D_Array.

On Error GoTo Txt

• The On Error statement is used to handle any error that may occur in the code.

    Dim myArr(), myArrx(), myArry() As Variant
    Dim myRng As Range, Name As Range
    Dim search_name, search_subject As String

• The variables are declared using the Dim statement. Three variables are declared as Variant: myArr, myArrx, and myArry. Also, two Range variables are declared: myRng and Name and String variables are:  search_name and search_subject.

      Set myRng = Sheets("Index_Match").Range("B4:E14")
      Set Name = Sheets("Index_Match").Range("B4:B14")

• The range variables are assigned the range of cells from which data is to be extracted using the Set statement.

    ReDim myArr(1 To myRng.Rows.Count, 1 To myRng.Columns.Count)

• A 2D array is created by using the ReDim statement with the parameters 1 to myRng.Rows.Count and 1 to myRng.Columns.Count.

    For I = 1 To myRng.Rows.Count
        For j = 1 To myRng.Columns.Count
            myArr(I, j) = myRng.Cells(I, j)
        Next j
    Next I

• A Nested For Loop goes through each element of the myRng range and puts the value of the cells into the corresponding myArr array element.    

    search_name = InputBox("Enter the name of the student:")
    search_subject = InputBox("Enter the subject:")

• User inputs are taken using the InputBox function and stored in variables search_name and search_subject.

    For k = 1 To UBound(myArr, 2)
        If myArr(1, k) = search_subject Then
            county = k
        End If
    Next k     

• Another loop is used to search for the column that contains the subject name in the 2D array.

    ReDim myArrx(1 To myRng.Rows.Count)
    For I = 1 To UBound(myArrx)
        myArrx(I) = myArr(I, county)
    Next I

• Two 1D arrays are created using the ReDim statement with the parameter 1 to myRng.Rows.Count. A loop is used to extract the scores for the searched subject from the 2D array and store them in the 1D array myArrx.

    ReDim myArry(1 To myRng.Rows.Count)
    For I = 1 To UBound(myArry)
        myArry(I) = myArr(I, 1)
    Next I

• Another loop is used to extract the names of students from the 2D array and store them in the 1D array myArry.

    With Application.WorksheetFunction
        score = .index(myArrx, .match(search_name, myArry, 0))
    End With

• The score of the searched student is obtained using the INDEX and MATCH functions with the searched name and 1D arrays as inputs.

    MsgBox ("The score is : " & score)

• The obtained score is displayed to the user using a MsgBox.

    Exit Sub

• The subroutine is ended using the Exit Sub statement.

Txt:
    MsgBox "Not Found”
End Sub

• In case of an error, a MsgBox is displayed with the text “Not Found” using the Txt label.

VBA Breakdown

Sub Index_Match_Multiple_Criteria()

• The first line defines a subroutine named Index_Match_Multiple_Criteria.

On Error GoTo Txt

• The second line starts an error handling block that directs the code to a specific label if an error occurs. The label is Txt.

    Dim myArr() As Variant
    Dim phy, chem As Integer
    Dim student As String
    Dim Name As Range, Physics As Range, Chemistry As Range

• The next few lines declare several variables used in the code. myArr is declared as a variant data type. Moreover, phy and chem are declared as Integer data types and student is a string data type. Also, Name, Physics, and Chemistry are declared as range data types.

    Set Name = Sheets("Index_Match").Range("B5:B14")
    Set Physics = Sheets("Index_Match").Range("C5:C14")
    Set Chemistry = Sheets("Index_Match").Range("D5:D14")

• The Set statements assign values to the Name, Physics, and Chemistry variables based on cell ranges in the worksheet.

    phy = InputBox("Score in Physics:")
    chem = InputBox("Score in Chemistry:")

• The next two lines prompt the user to enter values for phy and chem using an InputBox.

   ReDim myArr(1 To 2)

• The ReDim statement resizes the myArr array to have two elements.

    myArr(1) = phy
    myArr(2) = chem

• The next two lines assign the user-input values of phy and chem to the two elements of myArr.

    student = Application.Evaluate("INDEX(" & Name.Address & ", MATCH(1,(" & myArr(1) & "=" & Physics.Address & ")*(" & myArr(2) & "=" & Chemistry.Address & "), 0))")

• The next line uses the Application.Evaluate method to perform an INDEX-MATCH lookup with two criteria based on the values in myArr. The Address property is used to get the addresses of ranges Name, Physics, and Chemistry to be used in the formula. The result of the formula is assigned to the student variable.

    MsgBox ("Name of the Student:" & vbNewLine & student)

• The next line displays a MsgBox with the name of the student.

    Exit Sub
Txt:
    MsgBox "Not Found"  
End Sub

• The last few lines provide an error message if an error occurs during the execution of the code.

VBA Breakdown

Private Sub CommandButton1_Click()

• This line declares a private subroutine named CommandButton1_Click that is triggered when the user clicks on a command button on the worksheet.

On Error GoTo Txt

• This line tells the program to jump to the Txt section of the code if there’s an error.

    Dim myArr() As Variant
    Dim student As String
    Dim Name As Range, Physics As Range, Biology As Range

• These lines declare variables used in the subroutine. Specifically, myArr is a variant, student is a string, and Name, Physics, and Biology are ranges.

    Set Name = Sheets("Index_Match").Range("B5:B14")
    Set Physics = Sheets("Index_Match").Range("C5:C14")
    Set Biology = Sheets("Index_Match").Range("E5:E14")

• These lines set the values of the range variables Name, Physics, and Biology to the ranges specified in the worksheet INDEX_MATCH.   

    ReDim myArr(1 To 2)
    myArr(1) = TextBox1.Value
    myArr(2) = TextBox2.Value

• These lines create a dynamic array named myArr with two elements, then assign the values of two text boxes on the worksheet to those elements.

    student = Application.Evaluate("INDEX(" & Name.Address & ", MATCH(1,(" & myArr(1) & "=" & Physics.Address & ")*(" & myArr(2) & "=" & Biology.Address & "), 0))")

• This line uses the Evaluate method of the Application object to perform an INDEX-MATCH lookup using the values in myArr as criteria. It assigns the result to the student variable.

    MsgBox ("Name of the Student:" & vbNewLine & student)

• This line displays a MsgBox with the name of the student returned by the lookup.

    Exit Sub

• This line ends the subroutine.

Txt:
    MsgBox "Not Found"
End Sub

• This is the error handling section of the code. If there is an error during the execution of the code, it jumps to this section and displays a MsgBox with the text “Not Found“.

VBA Breakdown

Sub Index_Match_Table()

• This is a VBA subroutine named Index_Match_Table.

On Error GoTo Txt

• This line sets up an error handler to jump to a label named Txt in case any error occurs during the execution of the code.

    Dim myTable As ListObject
    Dim Ref As Long
    Dim Output As Variant
    Dim score As Integer

• These are variable declarations. myTable is a ListObject variable that will reference the table to be searched. Ref is a Long variable that will store the row number where the search term is found. Output is a Variant variable that will store the name of the student whose score matches the search term. score is an Integer variable that will store the search term entered by the user.

    Set myTable = Sheets("Table").ListObjects("Table1")

• This line sets the value of myTable to the table named Table1 on the worksheet named Table.

    score = Int(InputBox("Score in Chemistry:"))

• This line displays an InputBox to the user asking for the score in chemistry. The value entered by the user is converted to an Integer and stored in the score variable.

    With Application.WorksheetFunction
        Ref = .match(score, myTable.ListColumns("Chemistry").DataBodyRange, 0)
        Output = .index(myTable.ListColumns("Name").DataBodyRange, Ref)
    End With

• This is a With block that uses the Application.WorksheetFunction object to perform the MATCH and INDEX functions. It searches the Chemistry column of myTable for the value of score and returns the row number where it is found. This row number is stored in the Ref variable. The INDEX function then returns the value in the same row of the Name column of myTable, which is stored in the Output variable.

    MsgBox (Output & " has got that score")

• This line displays a MsgBox with the name of the student whose score matches the search term.

    Exit Sub

• This line indicates that the subroutine has finished executing and exits the subroutine.

Txt:
    MsgBox "Not Found"
End Sub

• This is a label that is jumped to if an error occurs during the execution of the code. It displays a MsgBox with the text “Not Found“.

VBA Breakdown

Sub Index_Match_Different_Sheets()

• This is the beginning of the code and the declaration of the subroutine. The name of the subroutine is Index_Match_Different_Sheets.

On Error GoTo Txt

• This line is used to handle any errors that may occur in the code. If an error is encountered, the code will jump to the label Txt (defined later in the code).

    Dim WS As Worksheet
    Dim search_name, sheet_name, score As String

• These lines are declaring variables that will be used in the code. WS is declared as a Worksheet object, and search_name, sheet_name, and score are declared as String data types.

    search_name = InputBox("Total score in 3 subjects" & vbNewLine & "Enter the name of the student:")
    sheet_name = InputBox("Enter the Worksheet name here:")

• These lines prompt the user for input. The first line will display a message in the InputBox asking for the name of a student. The second line will display a message asking for the name of a worksheet.

    Set WS = ThisWorkbook.Sheets(sheet_name)

• This line sets the variable WS to the worksheet with the name specified by the user input.    

    With Application.WorksheetFunction
        score = .index(WS.Range("D5:D14"), .match(search_name, WS.Range("B5:B14"), 0))
    End With

• This block of code uses the VBA INDEX MATCH function to find the total score of a student in three subjects. The range D5:D14 contains the scores for each subject, and the range B5:B14 contains the names of the students. The variable search_name is used to find the row number of the student’s name, and the score variable is set to the value in the same row in the range D5:D14.

    MsgBox (search_name & " has scored " & score & " in three subjects")

• This line displays a MsgBox with the name of the student and their total score in three subjects.  

    Exit Sub

• This line marks the end of the subroutine and exits the code.

Txt:
    MsgBox "Not Found"     
End Sub

• This block of code is the error handler that was defined earlier. If an error occurs, the code jumps to this label and displays a MsgBox with the text “Not Found“.

VBA Breakdown

Sub Index_Match_Another_Workbook()

• This is the beginning of the code and the declaration of the subroutine. The name of the subroutine is Index_Match_Another_Workbook.

On Error GoTo Txt

• This line is used to handle any errors that may occur in the code. If an error is encountered, the code will jump to the label Txt (defined later in the code).

    Dim WB As Workbook
    Dim FilePath As Variant
    Dim WS As Worksheet
    Dim search_name, score As String

• These lines are declaring variables that will be used in the code. WB is declared as a Workbook object, FilePath is declared as a Variant data type, WS is declared as a Worksheet object, and search_name and score are declared as String data types.

    search_name = InputBox("Letter Grade " & vbNewLine & "Enter the name of the student:")

• This line prompts the user for input. The InputBox will display a message asking for the name of a student.

    FilePath = Application.GetOpenFilename("Excel Files (*.xlsx), *.xlsx")
    Set WB = Workbooks.Open(FilePath)
    Set WS = WB.Sheets("Info")

• These lines open a dialog box that allows the user to select an Excel file. Once a file is selected, it is assigned to the variable FilePath. The Workbooks.Open method is then used to open the selected file, and the variable WB is set to the opened workbook. Finally, the Sheets method is used to set the variable WS to the worksheet named Info in the opened workbook.

    With Application.WorksheetFunction
        score = .index(WS.Range("E5:E14"), .match(search_name, WS.Range("B5:B14"), 0))
    End With

• This block of code uses the VBA INDEX MATCH function to find the letter grade of a student. The range E5:E14 contains the letter grades for each student, and the range B5:B14 contains the names of the students. The variable search_name is used to find the row number of the student’s name, and the score variable is set to the value in the same row in the range E5:E14.

  MsgBox (search_name & " has obtained " & score & " letter grade")

• This line displays a Msgbox with the name of the student and their letter grade.

    Exit Sub

• This line marks the end of the subroutine and exits the code.

Txt:
    MsgBox "Not Found"
End Sub

• This block of code is the error handler that was defined earlier. If an error occurs, the code jumps to this label and displays a MsgBox with the text “Not Found“.