How to Use Excel VBA INDEX MATCH with Array

Get FREE Advanced Excel Exercises with Solutions!

While working with large datasets in Excel, it is very common to look for specific values with certain criteria. INDEX and MATCH functions can be used together to retrieve values with specific criteria. These two functions are worksheet functions. But we can use INDEX and MATCH functions with an Excel VBA array. In this article, we will discuss how to use Excel VBA INDEX MATCH with array.


Excel VBA INDEX MATCH with Array: 4 Examples

In this article, we have discussed four easy examples to use VBA INDEX MATCH with an array in Excel. Here in the dataset, we have the score distribution of multiple students. The dataset contains their names and their scores in subjects like Physics, Chemistry, and Biology. We will use INDEX and MATCH functions with an array in Excel VBA to retrieve data from the dataset with specific criteria.

Dataset for VBA INDEX MATCH with Array


1. Using INDEX MATCH for One-Dimensional Array 

We will apply the following VBA code to use INDEX and MATCH functions with a one-dimensional array in Excel VBA.

VBA INDEX MATCH with 1D Array

  • Paste the following code in your VBA Editor and press the Run button or F5 key to run the code:
Sub Index_Match_1D_Array()
On Error GoTo Txt
    'variable declaration
    Dim myArr() As Variant
    Dim Name As Range, Physics As Range
    Dim search_name, score As String 
    'set variables
    Set Name = Sheets("Index_Match").Range("B5:B14")
    Set Physics = Sheets("Index_Match").Range("C5:C14")
    'user input
    search_name = InputBox("Physics Score" & vbNewLine & "Enter the name of the student:")
    '1D array formation
    ReDim myArr(1 To 1)
    myArr(1) = search_name
    'show score of the student
    With Application.WorksheetFunction
        score = .index(Physics, .match(myArr(1), Name, 0))
    End With
    MsgBox (myArr(1) & " has scored " & score & " in Physics") 
    Exit Sub
Txt:
    MsgBox "Not Found"
End Sub

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

In the prompt, put the name of the student and press OK.

InputBox with User Input for 1D Array

You will see a MsgBox showing how much the student has scored in physics. That’s how you can use VBA INDEX MATCH with array in Excel.

Output MsgBox of 1D array


2. Use of INDEX MATCH for Two-Dimensional Array 

We will apply the following VBA code to use INDEX and MATCH functions with a two-dimensional array in Excel VBA.

VBA INDEX and MATCH Functions with Two Dimensional Array

  • Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:
Sub Index_Match_2D_Array()
On Error GoTo Txt
    'variable declaration
    Dim myArr(), myArrx(), myArry() As Variant
    Dim myRng As Range, Name As Range
    Dim search_name, search_subject As String
    Set myRng = Sheets("Index_Match").Range("B4:E14")
    Set Name = Sheets("Index_Match").Range("B4:B14")
    '2D array formation
    ReDim myArr(1 To myRng.Rows.Count, 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
    'user input
    search_name = InputBox("Enter the name of the student:")
    search_subject = InputBox("Enter the subject:")
    'look for the subject in 2D array
    For k = 1 To UBound(myArr, 2)
        If myArr(1, k) = search_subject Then
            county = k
        End If
    Next k
    'get 1D array from 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
    'get score of the student
    With Application.WorksheetFunction
        score = .index(myArrx, .match(search_name, myArry, 0))
    End With
    MsgBox ("The score is : " & score)
    Exit Sub
Txt:
    MsgBox "Not Found" 
End Sub

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.

3. Combination of INDEX and MATCH Functions for Multiple Criteria with Array in Excel VBA

We will apply the following VBA code to use INDEX and MATCH functions with multiple criteria in Excel VBA.

VBA INDEX and MATCH Functions with Multiple Criteria with an ArrayEnter the following code in your VBA Editor and press the Run button or F5 key to run the code:

Sub Index_Match_Multiple_Criteria()
On Error GoTo Txt
    'variable declaration
    Dim myArr() As Variant
    Dim phy, chem As Integer
    Dim student As String
    Dim Name As Range, Physics As Range, Chemistry As Range
    'set variables
    Set Name = Sheets("Index_Match").Range("B5:B14")
    Set Physics = Sheets("Index_Match").Range("C5:C14")
    Set Chemistry = Sheets("Index_Match").Range("D5:D14")
    'user input
    phy = InputBox("Score in Physics:")
    chem = InputBox("Score in Chemistry:")
    'array formation
    ReDim myArr(1 To 2)
    myArr(1) = phy
    myArr(2) = chem
    'get score of the student
    student = Application.Evaluate("INDEX(" & Name.Address & ", MATCH(1,(" & myArr(1) & "=" & Physics.Address & ")*(" & myArr(2) & "=" & Chemistry.Address & "), 0))")
    MsgBox ("Name of the Student:" & vbNewLine & student)
    Exit Sub
Txt:
    MsgBox "Not Found"
End Sub

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.

Read More: VBA INDEX MATCH Based on Multiple Criteria in Excel


4. Create UserForm to Use INDEX and MATCH Functions with Array in Excel VBA

We will create a UserForm using INDEX and MATCH functions with an array in Excel VBA.

VBA INDEX and MATCH Functions with Array in a UserForm

Simply follow the steps below to create the UserForm:

  • Go to Insert > UserForm.

Creating New UserForm

  • From the Toolbox, you can choose different controls for your UserForm.

Toolbox of UserForm

  • We have inserted Labels to write instructions and TextBoxes to take user input. We have also inserted a CommandButton to see the result.

UserForm with ToolBox Controls

  • Double-click on the CommandButton. A new window will open. Enter the following code:
Private Sub CommandButton1_Click()
On Error GoTo Txt
    'variable declaration
    Dim myArr() As Variant
    Dim student As String
    Dim Name As Range, Physics As Range, Biology As Range
    'set variables
    Set Name = Sheets("Index_Match").Range("B5:B14")
    Set Physics = Sheets("Index_Match").Range("C5:C14")
    Set Biology = Sheets("Index_Match").Range("E5:E14")
    'array formation
    ReDim myArr(1 To 2)
    myArr(1) = TextBox1.Value
    myArr(2) = TextBox2.Value
    'get score of the student
    student = Application.Evaluate("INDEX(" & Name.Address & ", MATCH(1,(" & myArr(1) & "=" & Physics.Address & ")*(" & myArr(2) & "=" & Biology.Address & "), 0))")
    MsgBox ("Name of the Student:" & vbNewLine & student)
    Exit Sub
Txt:
    MsgBox "Not Found"
End Sub
  • Run the UserForm and follow the instructions shown in the video to see the output.

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

How to Use INDEX and MATCH Functions with Table in Excel VBA

We will apply the following VBA code to use INDEX and MATCH functions with a table in Excel VBA.

VBA INDEX and MATCH Functions with TableWe have made a table as shown below to use INDEX and MATCH functions with an array.

VBA INDEX and MATCH Functions with Table

Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:

Sub Index_Match_Table()
On Error GoTo Txt
    'variable declaration
    Dim myTable As ListObject
    Dim Ref As Long
    Dim Output As Variant
    Dim score As Integer
    'set values
    Set myTable = Sheets("Table").ListObjects("Table1") 
    'user input
    score = Int(InputBox("Score in Chemistry:"))
    'get output
    With Application.WorksheetFunction      
        Ref = .match(score, myTable.ListColumns("Chemistry").DataBodyRange, 0)
        Output = .index(myTable.ListColumns("Name").DataBodyRange, Ref)
    End With
    MsgBox (Output & " has got that score")
    Exit Sub  
Txt:
    MsgBox "Not Found"    
End Sub

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

In the prompt, put the score in chemistry and press OK.

InputBox with User Input in Excel TableYou will see a MsgBox showing the name of the student who has that score in chemistry. That’s how you can use VBA INDEX MATCH with array in Excel.

Output MsgBox


VBA INDEX MATCH for a Different Sheet in Excel

We can use VBA INDEX MATCH with multiple criteria for different sheets in Excel. Here in the dataset, we have the section, total marks obtained in three subjects and the letter grades of the students.

Dataset for VBA INDEX and MATCH Functions with Different SheetsWe will apply the following VBA code to use INDEX and MATCH functions with different sheets in Excel VBA.

VBA INDEX and MATCH Functions with Different Sheets

Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:

Sub Index_Match_Different_Sheets()
On Error GoTo Txt
    'variable declaration
    Dim WS As Worksheet
    Dim search_name, sheet_name, score As String
    'user input
    search_name = InputBox("Total score in 3 subjects" & vbNewLine & "Enter the name of the student:")
    sheet_name = InputBox("Enter the Worksheet name here:")
    Set WS = ThisWorkbook.Sheets(sheet_name)
    'show score of the student from different worksheets
    With Application.WorksheetFunction
        score = .index(WS.Range("D5:D14"), .match(search_name, WS.Range("B5:B14"), 0))
    End With
    MsgBox (search_name & " has scored " & score & " in three subjects")
    Exit Sub
Txt:
    MsgBox "Not Found"
End Sub

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 INDEX MATCH from Another Workbook in Excel

We will apply the following VBA code to use INDEX and MATCH functions with another workbook in Excel VBA. The dataset is the same as the previous one.

VBA INDEX and MATCH Functions with Another Workbook

Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:

Sub Index_Match_Another_Workbook()
On Error GoTo Txt
    'variable declaration
    Dim WB As Workbook
    Dim FilePath As Variant
    Dim WS As Worksheet
    Dim search_name, score As String
    'user input
    search_name = InputBox("Letter Grade " & vbNewLine & "Enter the name of the student:")    
    FilePath = Application.GetOpenFilename("Excel Files (*.xlsx), *.xlsx")
    Set WB = Workbooks.Open(FilePath)
    Set WS = WB.Sheets("Info")
    'show score of the student from another workbook
    With Application.WorksheetFunction
        score = .index(WS.Range("E5:E14"), .match(search_name, WS.Range("B5:B14"), 0))
    End With   
    MsgBox (search_name & " has obtained " & score & " letter grade")
    Exit Sub
Txt:
    MsgBox "Not Found"
End Sub

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

Read More: How to Use Excel VBA INDEX MATCH from Another Worksheet


Things to Remember

There are a few things to remember while using VBA INDEX MATCH based on multiple criteria with an array in Excel:

  • Assign the lookup value and data array properly.
  • Use the Application object to call INDEX and MATCH functions from the worksheet.
  • Use INDEX and MATCH functions in the correct order.
  • Specify if you want an exact match.

Frequently Asked Questions

1. What is the difference between the INDEX and MATCH functions?

The INDEX function is used to retrieve a value from a specific row and column in a range of cells or arrays, whereas the MATCH function is used to find the position of a value in a range of cells or arrays. So, when used together, the MATCH function can determine the row and column numbers from which the INDEX function needs to retrieve the desired value.

2. Can I use INDEX and MATCH functions with an array that is not rectangular?

Yes, you can use VBA INDEX MATCH with an array that is not rectangular. However, you need to ensure the array has the same number of elements in each row. Otherwise, the MATCH function may return unexpected results.

3. How do I handle errors when using INDEX and MATCH functions with an array in Excel VBA?

You can handle errors when using VBA INDEX MATCH with an array in Excel by using error handling techniques, such as On Error statements.


Download Practice Workbook


Conclusion

In this article, we have discussed 4 examples in detail to use VBA INDEX MATCH array in Excel. This article will allow users to use Excel more efficiently and effectively. If you have any questions regarding this essay, feel free to let us know in the comments.

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abu Sina Ibne Albaruni
Md. Abu Sina Ibne Albaruni

Md. Abu Sina Ibne Albaruni holds a BSc in Mechanical Engineering from Bangladesh University of Engineering and Technology. He is a dedicated employee of the ExcelDemy project. He has written quite a large number of articles for ExcelDemy. Besides, he has expertise in VBA. He efficiently automates Excel issues using VBA macros and actively engages in the ExcelDemy forum, offering valuable solutions for user interface challenges. His areas of interest in work and study span MATLAB, Machine Learning,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo