We cannot use the INDEX and MATCH functions directly in Excel VBA because they are not part of the Excel VBA built-in functions. We can however call them in VBA using the WorksheetFunction object. This tutorial shows an example of how to use Index Match in Excel VBA.
Example of How to Use Index Match in Excel VBA
The INDEX function returns a value or reference of the cell at the intersection of a particular row and column in a given range.
The MATCH function returns the relative position of an item in an array that matches a specified value in a specified order.
Suppose we have the following dataset showing various tablets and their RAM sizes.
We want to use a combination of INDEX and MATCH functions in Excel VBA to extract RAM values from the range A2:A10 and fill them in the range E2:E10.
We use the following steps:
- Click Developer >> Code >> Visual Basic to open the Visual Basic Editor.
Alternatively, you can also open the Visual Basic Editor by pressing the shortcut Alt + F11.
- In the Visual Basic Editor, click Insert >> Module to create a new module.
- Type the following sub-procedure in the module.
Dim k As Integer
For k = 2 To 10
Cells(k, 5).Value = WorksheetFunction.Index(Range("A2:A10"), _
WorksheetFunction.Match(Cells(k, 4).Value, Range("B2:B10"), 0))
- Save the procedure and save the workbook as a Macro-Enabled Workbook.
- Place the cursor anywhere in the procedure and press F5 to run the code.
- Click the View Microsoft Excel button on the toolbar to switch to the active workbook containing the dataset.
Alternatively, you can press Alt + F11 to switch to the active workbook.
Range E2:E10 is filled in with RAM values from range A2:A10:
Explanation of the sub-procedure
- The k integer variable is declared.
- The count values of rows 2 to 10 are assigned to the k variable.
- The WoksheetFunction object is used to call the INDEX and MATCH functions which are used in the statement that assigns a value to Cells(k,5).
- Cells(k, 5).Value = WorksheetFunction.Index(Range(“A2:A10”), WorksheetFunction.Match(Cells(k, 4).Value, Range(“B2:B10”), 0)) This is similar to E2.Value = WorksheetFunction.Index(“2:A10“, 1 ) which returns the value 4.
This tutorial has given an example of how to use a combination of the Index function and the Match function in Excel VBA. We hope that you found the tutorial useful.