How to Use Excel VBA Index Match with Array (4 Methods)

VBA Breakdown

Sub Index_Match_1D_Array()

• This marks the start of a Subroutine called Index_Match_1D_Array.

On Error GoTo Txt

• This is error-handling statement directs VBA to jump to the Txt label if an error occurs.

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

• These are variable declarations: 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")

• Setting variables: We assign values to Name and Physics based on specific ranges in the worksheet named Index_Match.

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

• User input:  The user inputs the name of a student.

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

• 1D array formation:  We create a one-dimensional array called myArr with one element containing the student’s name. 

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

• Calculating the score: Using the INDEX and MATCH functions, we find the student’s score in Physics.

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

• Displaying results:  A message box (MsgBox) shows the student’s name and their Physics score.

    Exit Sub

• This exits the subroutine.

Txt:
    MsgBox "Not Found"
End Sub

• If an error occurs, this label displays a “Not Found” message.

VBA Breakdown

Sub Index_Match_2D_Array()

• This starts the subroutine.

On Error GoTo Txt

• Error handling directs VBA to the Txt label if an error occurs.

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

• Variable declarations: We define arrays 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")

• Set ranges: Assign values to myRng and Name.

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

• Create a 2D array: Populate myArr with data from the specified range.

    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 input: Prompt the user for the student’s name and subject.

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

• Another loop – find the subject: locate the subject in the 2D array.

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

    ReDim myArry(1 To myRng.Rows.Count)

    For I = 1 To UBound(myArry)
        myArry(I) = myArr(I, 1)
    Next I

• Extract a 1D array: Create myArrx and myArry from the 2D array.

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

• Get the student’s score: Use INDEX MATCH to find the score.

    MsgBox ("The score is : " & score)

• Display the result: Show the student’s score in a message box..

    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()

• This marks the start of the subroutine.

On Error GoTo Txt

• Error handling directs VBA to the Txt label if an error occurs.

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

• Variable declarations: We define variables for scores phy and chem, student name, and ranges Name, Physics, and Chemistry.

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

• Set ranges: Assign values to the ranges based on cell locations in the worksheet.

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

• User input: Prompt the user to enter scores for Physics and Chemistry.

   ReDim myArr(1 To 2)

• Array formation: Create a two-element array (myArr) with the user-input scores.

    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))")

• Get the student’s score: Use INDEX and MATCH functions with multiple criteria.

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

• Display the result: Show the student’s name in a message box.

    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 declares a private subroutine triggered when the user clicks on a command button.

On Error GoTo Txt

• Error handling directs VBA to the Txt label if an error occurs.

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

• Variable declarations: We define variables for the array (myArr), student name (student), and ranges (Name, Physics, and Biology).

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

• Set ranges: Assign values to the ranges based on cell locations in the worksheet.

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

• Array formation: Create a dynamic array (myArr) with two elements, using values from TextBoxes.

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

• Get the student’s score: Use INDEX and MATCH functions with the specified criteria.

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

• Display the result: Show the student’s name in a message box.

    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 marks the start of the subroutine.

On Error GoTo Txt

• Error handling directs VBA to the Txt label if an error occurs.

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

• Variable declarations: We define variables for the table (myTable), row number (Ref), student name (Output), and the user’s input (score).

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

• Set values: Assign the table reference based on the worksheet and table name.

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

• User input: Prompt the user to enter the score in chemistry.

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

• Get the output: Use INDEX and MATCH functions to find the student name.

    MsgBox (Output & " has got that score")

• Display the result: Show the student’s name in a message box.

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

This code allows you to find a student’s alpha grade by matching their name across different workbooks. If the student’s name is not found, it will display a “Not Found” message.