VBA Vlookup

It was well known that the respective VLookup is a worksheet function in Microsoft Excel, but it could also be used in the VBA (Virtual Basic for Application). The functionality of the VLookup is similar to the feature in VBA and worksheet.

In Microsoft Excel, the VLookup function is primarily used to search for a value in an array and returns its corresponding value from another column. The search value should be present in the first column respectively.

The VLookup worksheet functions can be used in the VBA coding. The function can only call by using the worksheet, not built in the VBA.

Syntax

The VLookup function follows the following syntax in Microsoft Excel VBA:

Or

In which,

• Lookup_value: it is primarily a value that we are searching for.
• Table_array: It is considered to be the range that we are searching through.
• Col_index_num:It is the column number of the return value as well.
• Range_lookup: The Range_lookup tells whether the match is exact or approximate. It can be 0/1 or true/false; we can use "false" to find the exact match. And we can also make use of the "true" to assume that the first column is sorted numerically or alphabetically, respectively.

How to make use of the Vlookup function in Microsoft Excel VBA?

Step 1: First, we must click on the Developer tab and choose the Visual Basic option.

Step 2: After that, we must click the Insert button and add a Module.

Step 3: Then Write the VLookup program.

Step 4: Now run the code by just using the Run button.

Let see calling the VLookup function in Excel VBA with the help of some examples that are given below:

Examples

Let us assume that we have the employee's monthly salary data, and the table below displays the employee's salary using a dialog box.

The data is given in columns B and C. Employees ID in A cell, Name of employees in B cell, and the salary of the employees is returned in the C cell.

Step 1: Firstly, we need to write down the VLookup code. And then, we will define the range in which data are present, i.e., columns A, B, and C.

After that, we must define the "employee name" as cell B2 and "salary" as cell C11. Call the VLookup function using the WorksheetFunction and put it in Sal, respectively.

Step 2: As in the above example, we used a variable "E_name" to store the employee name whose salary is to be fetched. It gives the employee's NameName to the VLookup, and it returns that employee's salary.

And after that, we need to execute the code using the Run button. And we will get the output shown in the below screenshot.

Example 2: Here, we add a new column that is Department in the above table.

Step 1: We need to write the VLookup code. And define the range in which data are present, i.e., columns A, B, C, and D.

After that, we need to write a code to call the VLookup function using the WorksheetFunction based on the Employee ID.

Step 2: As in the above example, we asked the user to enter the employee id, and then we used multiple VLookup statements and all the details in a single message box.

Run the above code using the Run button, enter the employee ID we want to enter, and click the OK button.

Step 3: After that, we need to click on the OK button, and after that, we will get the output as depicted below: