Using Excel Worksheet Functions INDEX & MATCH in VBA Code!

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. Only a few worksheet functions are exempt from this rule, and this is in the case where there is already a VBA equivalent function.

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 utilize Excel Worksheet Functions in VBA code.

We are going to utilize the INDEX and MATCH Functions in our 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.

Using INDEX and MATCH Worksheet Functions 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.

Read More: Index Function Excel [Examples, Make Dynamic Range, INDEX MATCH]

1) 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.

Name of student, Gender, Eye Color
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))

Index and Match Functions Together!

3) Upon pressing CTRL-ENTER, we get the value of Female returned, as the gender as shown below.

Output of Index and Match Functions

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

Name of some students, their gender and their eye color

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

6) Now with the UserForm sheet activated, we go to Developer>Code>Visual Basic in order to open the Visual Basic Editor (VBE).

7) Once in the VBE interface, we go to Insert, UserForm as shown below.

Creating User Form in Excel VBA Editor

8) Using the Properties Window, we will rename our form to StudentLookup, change the Caption to Look up 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.

A userform is created in Excel VBA Editor

9) 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 center align the text. The special effect used will be the 1– fmSpecialEffectRaised as shown below.

A Label is inserted

10) Now we will insert a combo box below the label. Name this combo box cmdStudentName and for the RowSource, type StudentNames (this is the named ranged containing the student names that we named in the actual worksheet).

A combo box is inserted

11) In order to see the effect of setting the RowSource of the combo box, click the Run Sub/UserForm button as shown.

Creating a User Form in Excel VBA

12) 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 is now populated with the student names from the named range, automatically as shown below.

Lookup Student Information User Form

13) Close the UserForm by clicking on the close button. Press Alt-F11 in order to go back to the VBE.

Read More: VLOOKUP versus INDEX and MATCH versus DGET

14) 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 center align the text. The special effect used will be the 1– fmSpecialEffectRaised as shown below.

Adding Gender Information to the User Form

15) Create a textbox below the Gender label, and name it txtGender, as shown below.

Gender Form in Excel

16) 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.

Look up student information User Form

17) Now select all the controls, added to the UserForm, thus far using the control key.

Look up student information in Excel VBA Image 2

18) Center horizontally, as shown below.

Center Horizontally all the controls in the User Form

19) 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.

Look up student information in Excel VBA Image 3

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

Look up student information in Excel VBA Image 4

21) 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))
 
Let txtGender.Text = b
 
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

 

 

Raw Code in Excel VBA

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 get started with, since 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, as you become more experienced with VBA and the different data types, it is advisable to use one of the other more specific data types such as integer or string, since, for more advanced longer code, the variant data type does not use memory as efficiently as the other data types.

Variable a’s value is drawn 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 a student name is selected from the combo box on the UserForm then variable b’s value is attained by using the INDEX Worksheet Function in combination with the MATCH Function in the VBA code, as shown.

Read More: How to Create a Body Mass Index (BMI) Calculator in Excel Using VBA

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 a familiarity with the syntax gleaned from worksheet knowledge is recommended.  Variable c’s value is also attained 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’s value is sourced from the gender column in the worksheet, whereas variable c’s value is sourced 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.

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

23) Go to Developer>Controls> Insert> ActiveX Controls, and insert a button as shown.

Student Information Image 2

Student Information Image 3

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

Student Information Image 4

25) Change the Name of the button to cmdShowForm and the Caption to Look up Student Information.

Using INDEX and MATCH Worksheet Functions in Excel VBA

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

Student Information Image 5

27) Enter the following code:

Private Sub cmdShowForm_Click()
 
StudentLookup.Show
 
End Sub

Raw Excel VBA code image 2

28) Return to the worksheet and make sure Design Mode is unchecked and then click the button in order to show the form.

Lookup Student Information - Using Excel INDEX and MATCH Worksheet Functions in Excel VBA Code

Student Lookup Information Image 6

29) Select a student name using the combo box and the student gender and eye color is returned automatically.

Student Lookup Information Image 7

Remember to save your workbook as a macro-enabled workbook, if you haven’t done so already and there you have it, we have utilized the worksheet INDEX and MATCH Functions together in VBA in order to create a lookup form.

Download Working File

Using-Worksheet-Functionsin-VBA-Macro-Enabled

Conclusion

Excel has many useful worksheet functions, which can be utilized in VBA as well. These functions will allow you to extend your VBA code and if you already know how they work in a standard Excel worksheet then the learning curve is not that great, with respect to adapting the knowledge for VBA. Accessing the worksheet functions, in one’s VBA code can be a real time saver since then 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.

Useful Links

How to Use the Index Function

How to Use the Match Function

How to Use Index and Match together to perform lookups

Excel VBA Data Types: The Complete Guide To 15 Important Data Types

Complete list of Excel Worksheet Functions available to VBA

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.

In her spare time when she’s not exploring Excel or Access, she is into graphic design, amateur photography and caring for her two pets, Pretzel and Snoopy.

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *

Solve the Math * Time limit is exhausted. Please reload CAPTCHA.