How to Use the INDEX MATCH with Excel VBA - 9 Steps

Download Practice Workbook

Download the practice workbook and practice.

INDEX-MATCH.xlsx

Use the INDEX and MATCH Functions in a VBA code to create a simple UserForm.

Step1 – Apply the INDEX and the MATCH Functions

There are 2 sheets in the workbook. One is empty: UserForm, the other: StudentInformation contains a range showing student names, gender, and eye color.

Use the following formula to find the gender based on the student’s name:

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

Press CTRL-ENTER.
The value of Females is returned.

Step 2 – Change the Name of Column B into StudentNames

Name the range A2: A31, StudentNames.

Hide the StudentInformation sheet, by right-clicking and selecting Hide.

Step 3 – Open the Visual Basic Window

Go to Developer > Code > Visual Basic to open the Visual Basic Editor (VBE).
Go to Insert and select UserForm.

Step 4 – Change the Properties and Add Text Boxes

In the Properties Window, rename the 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 displayed, press F4.

Insert a label using the Toolbox (go to View, Toolbox).
Change the Caption to Choose a student.
Change the BackColor to white, here.
Set the font to Georgia, the font style to bold, the font size to 12, and choose center to align the text.
Use the special effect 1– fmSpecialEffectRaised.

Insert a combo box below the label. Name it cmdStudentName.
In RowSource, enter StudentNames.

Click Run Sub/UserForm.
By clicking the drop-down arrow in UserForm, the combo box automatically displays students’ names.

Close the UserForm by clicking the close button.
Press Alt-F11 to go back to the VBE.
Add another label to the UserForm (below the combo box) and change the Caption to Gender.
Change the BackColor to white, here.
Set the font to Georgia, the font style to bold, the font size to 12, and choose center to align the text
Use the special 1– fmSpecialEffectRaised.

Create a textbox below the Gender label. Name it txtGender.
Add another label called Eye Colour and a textbox named txtEyeColour.
Use the same properties for the labels.

Select all the controls added to the UserForm, using the control key.

Center horizontally.

Similar Readings

Step 5 – Add a Button from the Toolbox

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.
Change the Caption of the button to Look up Student Details.

Step 6 – Insert a VBA Code

Right-click the button and select View Code.

Enter the following code for the button click event:

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

Three variables are declared and the variant data type is assigned.

Draw a Variable value from the option selected in the drop-down combo box inn the UserForm. If there is no selection, all the other textboxes are empty.

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

It looks up the value using the same syntax as the worksheet function. Variable c draws value by using the INDEX Worksheet Function in combination with the MATCH Function in the VBA code when an option in the combo box is selected.

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

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

Go to the UserForm worksheeti.
Format it and insert the image provided by ExcelDemy.

Go to Developer > Controls > Insert > ActiveX Controls.

Insert a button.

Select the button and go to Developer > Controls > Properties.

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

Step 8 – View the Lookup Code

Right-click the button and select View Code.

Enter the following code:

Private Sub cmdShowForm_Click()
StudentLookup.Show
End Sub

Step 9 – Display the Final Result

Go back to the worksheet. Make sure to uncheck Design Mode.
Click the button in order to show the form.

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

Save your workbook as a macro-enabled workbook.

Practice Section: Examples

1) Set up a list of three items in column A: tangerines, carrots, and oranges. In the cell next to each item in column B list whether the items are fruits or vegetables. Use the INDEX & MATCH combination function to see whether carrots are fruits or vegetables.

2) Set up a list of ESPN in the NFL head coaches and the respective team they are coaching. Create a user form that allows the user to input a coach’s and see the team he is coaching in another textbox, by clicking submit. Use the INDEX & MATCH functions within the VBA code.

How to Use the INDEX MATCH with Excel VBA – 9 Steps