Vlookup in VBA

Excel VLOOKUP function looks for a value in the leftmost column of a table and then returns a value in the same row in a specified column. The table array must be sorted in ascending order if the function is to work properly.

An example use of the VLOOKUP function is to find a student’s name based on their student ID.

Explanation of the VLOOKUP function

The syntax of the function:

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

The function takes 4 arguments three of which are required and one optional.

The arguments are:

• lookup_value. This is the value that we want to look up.
• table_array. This is the data range that contains the lookup value. The lookup value should always be in the first column of the data range for the VLOOKUP function to work correctly. For instance, if our lookup value is in cell B2 then our table array or data range should begin with column B.
• col_index_num. This is the column number in the table array or data range that contains the return value. For example, if we specify C2:G10 as the table array, then column C is the first column, D is the second, and so on.
• range_lookup. This is an optional argument in which we can specify 1/TRUE if we want an approximate match or 0/FALSE if we want an exact match of the return value. If we omit this argument, the default value is always 1/TRUE for an approximate match.

In plain English the VLOOKUP function reads as follows:

=VLOOKUP(What we want to look up, where we want to look for it, the column number in the table that contains the value to return, return an Approximate or Exact match – displayed as 1/TRUE, OR 0/FALSE)

Use of VLOOKUP function in VBA

Excel VBA does not have an in-built VLOOKUP function. If we want to use it in VBA we have to call it using the WorksheetFunction property.

In this tutorial, we will explore the following 4 ways the VLOOKUP function can be used in VBA.

Way 1 – Search for information manually

We will use the following data set to explain how the VLOOKUP function can be used to look for information manually:

If we want, for example, to find out a student’s nationality using their student ID we use the following steps:

1. Press Alt + F11 to open the Visual Basic Editor (VBE). Alternatively, we can click Developer >> Code >> Visual Basic on the Excel Ribbon.

2. In the VBE Project Window, right-click ThisWorkbook object and click Insert Module on the shortcut menu:

Alternatively, click Insert >> Module on the menu bar:

3. In the new module, type in the following code:

4. To run the procedure place the cursor inside the procedure and press F5 on the keyboard or press the Run button on the toolbar.

A message box will pop up indicating the nationality of the Student ID 1239 we hard-coded in the procedure.

5. Press the OK button to go back to the procedure. If we want to find the nationality of another student we have to assign that student’s ID to the studentID variable by typing it in manually.

Explanation of the findStudentNationality procedure

• Three variables are declared with their data types.
• A student ID is assigned to the studentID variable. This is hard-coded so it has to be manually changed if we want to look for information for a different student.
• The data range or table array is assigned to the myRange variable using the Set keyword.
• studentNationality = Application.WorksheetFunction.VLookup(studentID, myRange, 5, False). In this statement, the Excel Vlookup function is called using the WorksheetFunction property. The value returned by the Vlookup function is assigned to the studentNationality variable.
• MsgBox “Student ID: ” & studentID & ” Nationality: ” & studentNationality & “”. When the procedure is run, the MsgBox function displays the value in the studentNationality variable in a message box.

Way 2 – Find information based on input entered into cells

We will use the following dataset to explain the second way that the VLOOKUP function can be used in Excel VBA:

1. From the active worksheet open the VBE using any of the steps explained before.
2. In the module inserted before type in the code below:

3. Press Alt + F11 to switch back to the active worksheet. Alternatively, we can click the View Microsoft Excel button on the toolbar:

4. In the dataset select cell D8 and type in the Student ID for the student for whom we want to find the Birth Date as in the example below:

5. Press Alt + F8 to open the Macro dialog box. Alternatively, click Developer >> Code >> Macros:

Or click View >> Macros >> Macros:

6. In the Macro dialog box select the findStudentBirthDate Macro and click Run:

The Birth Date of the student with the Student ID is inserted in cell D9 in the yyyy-dd-mm format:

To find the Birth Date of another student, we have to input that Student’s ID and run the macro again.

Explanation of the findStudentBirthDate procedure

• Three object variables are declared and different data ranges are assigned to them using the Set keyword.
• studentBirthDate.Value = Application.WorksheetFunction.VLookup(studentID, myRange, 4, False). In this statement, the Excel Vlookup function is called using the WorksheetFunction property. The value returned by the Vlookup function is assigned to the value property of the studentBithDate variable.

Way 3 – Find information using a macro attached to a button

We will use the following dataset that is on Sheet7 to explain how we can apply this method in looking for information. In this method we press a button to run the macro:

1. Click Developer >> Insert >> Active X >> Command Button:

2. Drag and draw the button on the worksheet:

3. Click Developer >> Controls >> Properties to open the properties box of the command button:

4. In the Properties box change the Name property of the command button to cmdBirthDate and the Caption property to Find Birth Date:

5. Right-click the command button and click View Code on the shortcut menu:

6. In the Private subroutine that is created type in the following code:

7. Press Alt + F11 or click the View Microsoft Excel button on the toolbar to switch back to the active worksheet.
8. Select cell D8 and key in a Student ID and click the command button:

The Birth Date of the student is inserted in cell D9. We can change the Student ID in cell D8 and press the command button again to find the Birth Date of another student.

Way 4 – Find information using the combination of the InputBox function and the VLOOKUP function

We can use the InputBox function to prompt for a value and then use the value as a lookup value in the VLOOKUP function to look for information.

We will use the following dataset to show how this method can be used:

We use the following steps:

1. Open the VBE using any of the methods explained earlier.
2. In the module inserted before, type in the following code:

3. Place a cursor inside the procedure and press F5 to run it.
4. Enter a Student ID in the input box and click OK.

The message box is displayed showing the Birth Date of the student whose ID was entered in the InputBox:

If we enter a nonexistent student ID, an error message is displayed:

Explanation of the findStudentBirthDate2 procedure

• Four variables are declared with their data types.
• On Error GoTo Message. This statement directs the code to the section labeled Message If a runtime error occurs during the execution of code.
• studentID = InputBox(“Enter ID of the student”). The value entered by the user in the InputBox is assigned to studentID variable.
• If studentID = “” Then Exit Sub. If the user clicks the Cancel button in the InputBox the procedure ends.
• lookupValue = CLng(studentID). The CLng function converts the value in the studentID variable to the Long data type and then this value is assigned to the lookupValue variable.
• studentBirthDate = Application.WorksheetFunction.VLookup(lookupValue, Range(“A2:E6”), 4, False). The value returned by the VLOOKUP function is assigned to studentBirthdate variable.
• MsgBox “Student ID: ” & studentID & ” Birth Date: ” & studentBirthDate & “”. The MsgBox function returns and displays the value in the studentBirthDate variable.
• If Err.Number = 1004 Then MsgBox (“Student data not found”). Excel Error Code 1004 means that the searched value is not found.

Conclusion

Excel VBA does not have an in-built VLOOKUP function. If we want to use it in VBA we have to call it using the WorksheetFunction property.

In this tutorial we have looked at 4 ways we can use the VLOOKUP function in Excel: search for information manually, search for information based on input in cells, search for information using a macro attached to a button, and search for information using a combination of VLOOKUP and InputBox functions.