How to Use INDEX MATCH with Excel VBA

You can utilize the built-in Excel Worksheet functions such as the VLOOKUP Function, the CHOOSE Function and the PMT Function in your VBA code and applications as well. In fact, most of the Excel worksheet functions can be accessed and used in VBA code. Like use INDEX & MATCH Worksheet Functions in Excel VBA code.

Why would you want to use Excel Worksheet functions in your VBA code? Well to extend the functionality of the code you are using. Also, you don’t have to come up with your own functions, unless you really need to, if the functionality is already there. All you basically need to do is access the function you need since it’s already there and there is then no need to reinvent the wheel.

So, let’s get started with an example showing how to use INDEX  MATCH with Excel VBA.

We are going to utilize the INDEX and MATCH Functions in Excel VBA code, in order to create a simple UserForm. Using the form, the user selects a name of the student, and then the corresponding gender of said student and eye color is retrieved and returned.


Download Practice Workbook

Please download the practice workbook to practice yourself.


9 Easy Steps to Use INDEX and MATCH Within VBA Code

The INDEX and MATCH Functions are often used in combination in formulas, in order to perform advanced lookups. The two in combination offer certain advantages over VLOOKUP.

We have already covered in detail, how to use INDEX and MATCH to perform advanced lookups in an Excel workbook as a straight worksheet formula, in a previous tutorial. We are now going to see how to use the INDEX and MATCH Functions together in VBA code, in order to confer similar functionality to the look up UserForm we are going to create.

Step1: Apply INDEX and MATCH Functions in Dataset

  • We are starting off with two sheets in our macro-enabled workbook. One is an empty sheet called UserForm, the other is a sheet called StudentInformation, which contains a range showing student names, their corresponding gender, and eye color as shown below.

Dataset to use Index and match function in excel vba

Let’s remind ourselves quickly if we wanted to use the INDEX and MATCH Functions in one formula, in the actual worksheet to give us the gender of the name of the student we want to look up. We would use the following formula:

=INDEX(B2:B31, MATCH("Diana Graham", A2:A31, 0))

use Index and match function in excel vba

  • Upon pressing CTRL-ENTER, we get the value of Females returned, as the gender as shown below.

Index and match function in excel vba

Step 2: Change the Name of B Column into StudentNames

  • We will now name the range A2: A31, StudentNames as shown below.

 Index and match function in excel vba

  • Hide the StudentInformation sheet, by right-clicking and selecting Hide. It’s a good idea to superficially hide the back-end worksheets that contain the information, that you don’t want the user to edit or see.

Step 3: Open Visual Basic Window

  • Now with the UserForm sheet activated, we go to Developer > Code > Visual Basic in order to open the Visual Basic Editor (VBE).
  • Once in the VBE interface, we go to Insert, UserForm as shown below.

Step 4: Change Properties and Add Text Boxes

  • Using the Properties Window, we will rename our form to StudentLookup, change the Caption to Lookup Student Information, change the BackColor to light blue, and set the height to 300 px and the width to 350 px. If the Properties Window is not showing up, press the F4 key on your keyboard in order to see it.

Textbox to use Index and match function in excel vba

  • We will now insert a label using the Toolbox (if you cannot see the Toolbox, for some reason go to View, Toolbox), change the Caption to Choose a student and we will change the BackColor to white in this case. We will set the font to Georgia, the font style to bold, the font size to 12, and the center align the text. The special effect used will be the 1– fmSpecialEffectRaised as shown below.

Textbox to use Index and match function in excel vba

  • Now we will insert a combo box below the label. Name this combo box cmdStudentName and for the RowSource, type StudentNames.

Textbox to use Index and match function in excel vba

  • In order to see the effect of setting the RowSource of the combo box, Click the Run Sub/UserForm button .
  • Now because of setting the RowSource to the named range, when the user clicks on the drop-down arrow on the UserForm, the combo box shows the student names from the named range, automatically as shown below.

Textbox to use Index and match function in excel vba

  • Close the UserForm by clicking on the close button. Press Alt-F11 in order to go back to the VBE.
  • Once back in the VBE, add another label to the UserForm (below the combo box) and change the Caption to Gender and we will change the BackColor to white in this case. We will set the font to Georgia, the font style to bold, the font size to 12, and the center align the text. The special effect used will be the 1– fmSpecialEffectRaised as shown below.

Textbox to use Index and match function in excel vba

  • Create a textbox below the Gender label, and name it txtGender.
  • Add another label called Eye Colour and a textbox named txtEyeColour as shown below. Use the same properties for the label as for the two other labels previously added to the form, in order to ensure that the UserForm has a consistent look.

Textbox to use Index and match function in excel vba

  • Now Select all the controls, added to the UserForm, thus far using the control key.

  • Center horizontally, as shown below.

Alignment of checkboxes to use Index and match function in excel vba


Similar Readings


Step 5: Add a Button from Toolbox

  • Next, Add a button to the form using the Toolbox. Change the Name of the button to cmdLookUp, the BackColor to light orange, keep the Tahoma font and change the style to bold, finally change the Caption of the button to Look up Student Details as shown below.

Step 6: Insert VBA Code

  • Right-click, the newly added button, and select View Code.

VBA code to Index and match function in excel vba

  • Enter the following code for the button click event:

Raw Code in Excel VBA

Dim a As Variant
Dim b As Variant
Dim c As Variant
a = cmdStudentName.Value
Sheets("StudentInformation").Activate
If a = "" Then
b = ""
Let txtGender.Text = b
c = ""
Let txtEyeColour.Text = c
Else
b = Application.WorksheetFunction.Index(Sheets("StudentInformation").Range("B2:B31"), Application.WorksheetFunction.Match(a, Sheets("StudentInformation").Range("A2:A31"), 0))
c = Application.WorksheetFunction.Index(Sheets("StudentInformation").Range("C2:C31"), Application.WorksheetFunction.Match(a, Sheets("StudentInformation").Range("A2:A31"), 0))
Let txtEyeColour.Text = c
End If

We start off by declaring three variables and assigning the variant data type to these declared variant data types. The variant data type is a good data type to start with. Because when working with worksheet functions, you may not always be sure of the outputs. Therefore use the variant data type, when you are starting out.

Later on, it is advisable to use one of the other more specific data types such as integer or string. For more advanced longer code, the variant data type does not use memory as efficiently as the other data types.

Variable a draw the value from the option the user selects in the drop-down combo box on the UserForm. If there is no selection, then all the other textboxes are empty.

If you select a student name from the combo box on the UserForm, then variable b draws value by using the INDEX Worksheet Function in combination with the MATCH Function in the VBA code, as shown.

It looks up the value using basically the same syntax as the worksheet function.  When using worksheet functions in VBA, the VBA IntelliSense in this particular case is not very intuitive, therefore familiarity with the syntax gleaned from worksheet knowledge is recommended.  Variable c draws value by using the INDEX Worksheet Function in combination with the MATCH Function in the VBA code when the user selects an option from the combo box.

Variable b attains value from the gender column in the worksheet, whereas variable c gets value from the Eye colour column in the worksheet.

The gender textbox is populated with b’s value and the eye color textbox is populated with c’s value.

Step 7: Insert a Command Button

  • Now go to the worksheet called UserForm in your workbook. Format it, as shown below, and insert the image provided by ExcelDemy.

Student Information Image 1

  • Go to Developer > Controls > Insert > ActiveX Controls.

Student Information Image 2

  • Insert a button as shown.

Student Information Image 3

  • With the button selected, go to Developer > Controls > Properties.

Student Information Image 4

  • Change the Name of the button to cmdShowForm and the Caption to Lookup Student Information.

Properties to use Index and match function in excel vba

Step 8: View Lookup Code

  • Right-click the button and select View Code as shown below.

View code to Index and match function in excel vba

  • Enter the following code:
Private Sub cmdShowForm_Click()
StudentLookup.Show
End Sub

Index and match function in excel vba

Step 9: Exhibit Final Result

  • Return to the worksheet. Make sure the unchecked Design Mode. Index and match function in excel vba
  • Click the button in order to show the form.

Index and match function in excel vba

  • Select a student’s name using the combo box. The code will return the student’s gender and eye color automatically.

Index and match function in excel vba

Remember to save your workbook as a macro-enabled workbook, if you haven’t done so already and there you have it, we use INDEX & MATCH Worksheet Functions in Excel VBA code in order to create a lookup form.


Read More: Excel INDEX-MATCH Formula to Return Multiple Values Horizontally


Conclusion

Excel has many useful worksheet functions, which can be utilized in VBA, as like as, using INDEX & MATCH Worksheet Functions in Excel VBA code. These functions will allow you to extend your VBA code. If you already know how they work in a standard Excel worksheet then the learning curve is not that great by adapting the knowledge for VBA. Accessing the worksheet functions, in one’s VBA code can be a real time-save. Because one does not have to develop custom functions for functionality that is already there.

Please feel free to comment and tell us if you use worksheet functions in your VBA code and applications.


Review Section: Test Your Understanding

1) Setup a simple list in column A of three items namely tangerines, carrots, and oranges, then in the cell next to each item in column B list whether the items in column A are fruits or vegetables, once you’ve completed setting up your sample data, use the INDEX & MATCH combination function to deliver whether carrots are fruits or vegetables.

2) Use this data set from ESPN on the NFL head coaches and the respective team they are coaching. Create a user form that allows a user to input the name of a certain coach in a textbox. Then have the team he is coaching delivered in another textbox when the user clicks submit. Use INDEX & MATCH worksheet function combination within your VBA code.


Further Readings

Taryn N

Taryn N

Taryn is a Microsoft Certified Professional, who has used Office Applications such as Excel and Access extensively, in her interdisciplinary academic career and work experience. She has a background in biochemistry, Geographical Information Systems (GIS), and biofuels. She enjoys showcasing the functionality of Excel in various disciplines. She has over ten years of experience using Excel and Access to create advanced integrated solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo