This will return the value (output of the Vlookup function) in the cell G4. Now, call the VLOOKUP function using WorksheetFunction in VBA and input it in salary.Value.Define the name of the employee and input the name from the cell F4.Define the range in which the values are present, i.e., columns B and C.
#CREATING VLOOKUP IN EXCEL 2016 CODE#
Now, you need to write a VBA VLOOKUP code such that given the name of the employee in a cell, F4, the salary of the employee will be returned in the cell G4. Suppose you have the data on the names of employees and their salary. You will notice that a message box will appear in the Excel sheet. MsgBox “Student with ID:” & student_id & ” obtained ” & marks & ” marks”Ī student with ID:11004 obtained 85 marks. In order to print the marks in a message box, let us use the following command: Since our data is present in the cells B4:D8, we define a range- myrange as:įinally, we input the VLOOKUP function using the Worksheet function in a variable, marks as:
Next, we define the range in which the value and the return value exist. Marks = (student_id, myrange, 3, False)įirstly, define a student id, which is the value to lookup.
#CREATING VLOOKUP IN EXCEL 2016 HOW TO#
In VBA code, the VLOOKUP function can be used as:Ī(lookup_value, table_array, col_index_num, range_lookup) How to Use VLookup in Excel VBA?īelow are some examples of the VLookup Code in Excel VBA. In which, lookup_value is the value to look for, table_arrray is the table, col_index_num is the column number of the return value, range_lookup signifies if the match is exact or approximate. The VLOOKUP function in Excel has the following syntax: The function is not built-in VBA and thus can only call using the worksheet. The worksheet function VLOOKUP can be used in VBA coding. It is also required to mention whether to look for an exact match or an approximate match. The value to search should be present in the first column. Read more is used to search a value in an array and return its corresponding value from another column. For instance, employee ID, student roll number, customer contact number, seller email address, etc., are unique identifiers. A unique identifier is uniquely associated with all the records of the database. The VLOOKUP function in Excel VLOOKUP Function In Excel The VLOOKUP excel function searches for a particular value and returns a corresponding match based on a unique identifier. Vlookup is a worksheet function in excel but it can also be used in VBA, the functionality of Vlookup is similar to the functionality in VBA and in worksheet both, as it is a worksheet function the method to use Vlookup in VBA is through Application.WorksheetFunction method and the arguments remain the same.